Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.2.5 INSERT 構文

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

または:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

または:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

INSERT は、既存のテーブルに新しい行を挿入します。このステートメントの INSERT ... VALUES および INSERT ... SET 形式は、明示的に指定された値に基づいて行を挿入します。INSERT ... SELECT 形式は、別の 1 つまたは複数のテーブルから選択された行を挿入します。INSERT ... SELECT については、セクション13.2.5.1「INSERT ... SELECT 構文」でさらに詳細に説明されています。

MySQL 5.6.2 以降では、パーティション化されたテーブルに挿入する場合、どのパーティションおよびサブパーティションが新しい行を受け入れるかを制御できます。PARTITION オプションは、テーブルの 1 つ以上のパーティションまたはサブパーティション (またはその両方) の名前のカンマ区切りリストを受け取ります。特定の INSERT ステートメントによって挿入される行がリストされているいずれかのパーティションに一致しない場合、INSERT ステートメントは Found a row not matching the given partition set エラーで失敗します。詳細および例については、セクション19.5「パーティション選択」を参照してください。

古い行を上書きするには、INSERT の代わりに REPLACE を使用できます。REPLACE は、古い行を複製する一意のキー値を含む新しい行の処理において INSERT IGNORE に相当するものです。新しい行は、破棄されるのではなく、古い行を置き換えるために使用されます。セクション13.2.8「REPLACE 構文」を参照してください。

tbl_name は、行が挿入されるテーブルです。このステートメントによって値が提供されるカラムは、次のように指定できます。

  • テーブル名のあとにカラム名のカンマ区切りリストを指定できます。この場合は、指定された各カラムの値を VALUES リストまたは SELECT ステートメントで指定する必要があります。

  • INSERT ... VALUES または INSERT ... SELECT にカラム名のリストを指定しない場合は、テーブル内のすべてのカラムの値を VALUES リストまたは SELECT ステートメントで指定する必要があります。テーブル内のカラムの順序がわからない場合は、DESCRIBE tbl_name を使用して見つけます。

  • SET 句は、カラム名を明示的に示します。

カラム値は、次のいくつかの方法で指定できます。

  • 厳密な SQL モードで実行していない場合、値が明示的に指定されていないカラムはすべて、デフォルトの (明示的または暗黙的な) 値に設定されます。たとえば、テーブル内のすべてのカラムを指定していないカラムリストを指定した場合、指定されていないカラムはそのデフォルト値に設定されます。デフォルト値の割り当てについては、セクション11.6「データ型デフォルト値」で説明されています。セクション1.8.3.3「無効データの制約」も参照してください。

    デフォルト値が含まれていないすべてのカラムの値を明示的に指定しないかぎり、INSERT ステートメントでエラーが生成されるようにする場合は、厳密モードを使用するようにしてください。セクション5.1.7「サーバー SQL モード」を参照してください。

  • カラムを明示的にそのデフォルト値に設定するには、キーワード DEFAULT を使用します。これにより、テーブル内の各カラムの値が含まれていない不完全な VALUES リストを書かなくても済むため、いくつかのカラムを除くすべてのカラムに値を割り当てる INSERT ステートメントの記述が容易になります。そうでない場合は、VALUES リスト内の各値に対応するカラム名のリストを書き出す必要があります。

    また、特定のカラムのデフォルト値を生成する式で使用できるより一般的な形式として DEFAULT(col_name) を使用することもできます。

  • カラムリストと VALUES リストの両方が空である場合、INSERT は、各カラムがそのデフォルト値に設定された行を作成します。

    INSERT INTO tbl_name () VALUES();
    

    厳密モードでは、いずれかのカラムにデフォルト値が含まれていない場合、エラーが発生します。それ以外の場合、MySQL は、明示的に定義されたデフォルト値が含まれていないすべてのカラムに対して暗黙のデフォルト値を使用します。

  • expr を指定して、カラム値を指定できます。これには、式の型がカラムの型に一致しない場合は型変換が行われる可能性があり、特定の値の変換によって、データ型に応じて異なる値が挿入されることがあります。たとえば、文字列 '1999.0e-2'INTFLOATDECIMAL(10,6)、または YEAR カラムに挿入すると、それぞれ、値 199919.992119.992100、および 1999 が挿入されます。INT および YEAR カラムに格納される値が 1999 である理由は、文字列から整数への変換では、その文字列の最初の、有効な整数または年と見なすことができる部分だけが調べられるためです。浮動小数点および固定小数点数カラムの場合、文字列から浮動小数点への変換では、文字列全体を有効な浮動小数点値と見なします。

    expr は、以前に値リスト内に設定された任意のカラムを参照できます。たとえば、次のステートメントは、col2 の値が、前に割り当てられている col1 を参照しているため実行可能です。

    INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
    

    ただし、次のステートメントは、col1 の値が、col1 のあとに割り当てられている col2 を参照しているため正当ではありません。

    INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
    

    1 つの例外として、AUTO_INCREMENT 値を含むカラムがあります。AUTO_INCREMENT 値はほかの値の割り当てのあとに生成されるため、割り当て内の AUTO_INCREMENT カラムへの参照はすべて 0 を返します。

VALUES 構文を使用する INSERT ステートメントは複数の行を挿入できます。これを行うには、それぞれが括弧で囲まれ、カンマで区切られた、カラム値の複数のリストを含めます。例:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

各行の値リストは、括弧で囲まれている必要があります。次のステートメントは、リスト内の値の数がカラム名の数に一致しないため不正です。

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);

このコンテキストでは、VALUEVALUES のシノニムです。どちらも、値リストの数については何も示しておらず、値リストが 1 つの場合でも複数の場合でも使用できます。

INSERT に関して影響を受けた行の値は、ROW_COUNT() 関数 (セクション12.14「情報関数」を参照してください) または mysql_affected_rows() C API 関数 (セクション23.8.7.1「mysql_affected_rows()」を参照してください) を使用して取得できます。

INSERT ... VALUES ステートメントを複数の値リストまたは INSERT ... SELECT とともに使用した場合、このステートメントは、次の形式の情報文字列を返します。

Records: 100 Duplicates: 0 Warnings: 0

Records は、このステートメントによって処理された行数を示します。(これは、Duplicates が 0 以外であることがあるため、必ずしも実際に挿入された行数ではありません。)Duplicates は、何らかの既存の一意のインデックス値を複製しているために挿入できなかった行数を示します。Warnings は、何らかの点で問題があったカラム値を挿入するための試行回数を示します。警告は、次のいずれかの条件で発生する場合があります。

  • NOT NULL として宣言されているカラムへの NULL の挿入。複数行の INSERT ステートメントまたは INSERT INTO ... SELECT ステートメントの場合、このカラムは、そのカラムデータ型の暗黙のデフォルト値に設定されます。これは、数値型では 0、文字列型では空の文字列 ('')、および日付と時間型では0の値です。サーバーは SELECT からの結果セットを検査して、それが単一行を返すかどうかを確認しないため、INSERT INTO ... SELECT ステートメントは複数行の挿入と同じ方法で処理されます。(単一行の INSERT の場合は、NULLNOT NULL カラムに挿入されても警告は発生しません。代わりに、このステートメントがエラーで失敗します。)

  • 数値カラムの、そのカラムの範囲外にある値への設定。この値は、その範囲のもっとも近い端点にクリップされます。

  • 数値カラムへの '10.34 a' などの値の割り当て。後続の非数値のテキストは取り除かれ、残りの数値部分が挿入されます。文字列値に先頭の数値部分が含まれていない場合、このカラムは 0 に設定されます。

  • 文字列カラム (CHARVARCHARTEXT、または BLOB) への、そのカラムの最大長を超える文字列の挿入。この値は、そのカラムの最大長に切り捨てられます。

  • 日付または時間カラムへの、そのデータ型として不正な値の挿入。このカラムは、その型の適切な 0 の値に設定されます。

C API を使用している場合は、mysql_info() 関数を呼び出すことによって情報文字列を取得できます。セクション23.8.7.35「mysql_info()」を参照してください。

INSERTAUTO_INCREMENT カラムを含むテーブルに行を挿入した場合、そのカラムに使用された値は SQL の LAST_INSERT_ID() 関数を使用して検索できます。C API 内からは、mysql_insert_id() 関数を使用します。

注記

これらの 2 つの関数が、必ずしも同じ動作を行うとは限りません。AUTO_INCREMENT カラムに関連した INSERT ステートメントの動作については、セクション12.14「情報関数」およびセクション23.8.7.37「mysql_insert_id()」でさらに詳細に説明されています。

INSERT ステートメントは、次の修飾子をサポートします。

  • DELAYED キーワードを使用した場合は、挿入される 1 つまたは複数の行をサーバーがバッファーに配置するため、INSERT DELAYED ステートメントを発行しているクライアントはただちに続行できます。そのテーブルが使用中である場合、サーバーはそれらの行を保持します。そのテーブルが未使用である場合、サーバーは行の挿入を開始する一方、そのテーブルに対する新しい読み取り要求が存在するかどうかを定期的にチェックします。存在する場合は、そのテーブルがふたたび未使用になるまで、遅延された行のキューは中断されます。セクション13.2.5.2「INSERT DELAYED 構文」を参照してください。

    DELAYED は、INSERT ... SELECT または INSERT ... ON DUPLICATE KEY UPDATE では無視されます。

    DELAYED はまた、テーブルやトリガーにアクセスする関数を使用しているか、または関数やトリガーから呼び出された INSERT でも無視されます。

    注記

    MySQL 5.6.6 現在、INSERT DELAYED は非推奨であり、将来のリリースで削除されます。代わりに INSERT (DELAYED を付けない) を使用してください。

  • LOW_PRIORITY キーワードを使用した場合、INSERT の実行は、ほかのどのクライアントもそのテーブルから読み取らなくなるまで遅延されます。これには、既存のクライアントが読み取っている間や、INSERT LOW_PRIORITY ステートメントが待機している間に読み取りを開始したほかのクライアントが含まれます。そのため、読み取り負荷の高い環境では、INSERT LOW_PRIORITY ステートメントを発行したクライアントが非常に長い時間 (場合によっては無期限に) 待機することになるおそれがあります。(これは、クライアントをただちに続行できるようにする INSERT DELAYED とは対照的です。)MyISAM テーブルで LOW_PRIORITY を使用すると、並列挿入が無効になるため、通常はこれを行わないようにしてください。セクション8.10.3「同時挿入」を参照してください。

    HIGH_PRIORITY を指定すると、サーバーが --low-priority-updates オプションで起動されている場合に、その効果がオーバーライドされます。また、同時挿入も使用されなくなります。セクション8.10.3「同時挿入」を参照してください。

    LOW_PRIORITYHIGH_PRIORITY は、テーブルレベルのロックのみを使用するストレージエンジン (MyISAMMEMORYMERGE など) にのみ影響を与えます。

  • IGNORE キーワードを使用した場合、INSERT ステートメントの実行中に発生したエラーは無視されます。たとえば、IGNORE を使用しない場合は、テーブル内の既存の UNIQUE インデックスまたは PRIMARY KEY 値を複製する行によって重複キーエラーが発生し、このステートメントは中止されます。IGNORE を指定すると、その行が破棄され、エラーは発生しません。代わりに、無視されたエラーが警告を生成する可能性がありますが、重複キーエラーは生成しません。

    IGNORE には、特定の値に一致するパーティションが見つからないパーティション化されたテーブルへの挿入でも同様の効果があります。IGNORE を指定しない場合、このような INSERT ステートメントはエラーで中止されます。ただし、INSERT IGNORE が使用されている場合は、一致しない値を含む行に対する挿入操作が暗黙のうちに失敗しますが、一致した行はすべて挿入されます。例については、セクション19.2.2「LIST パーティショニング」を参照してください。

    IGNORE が指定されていない場合は、エラーをトリガーするデータ変換によってステートメントが中止されます。IGNORE を指定すると、無効な値はもっとも近い値に調整されて挿入されます。警告は生成されますが、ステートメントは中止されません。mysql_info() C API 関数を使用すると、テーブルに実際に挿入された行数を確認できます。

  • ON DUPLICATE KEY UPDATE を指定したとき、UNIQUE インデックスまたは PRIMARY KEY に重複した値を発生させる行が挿入された場合は、古い行の UPDATE が実行されます。行ごとの影響を受けた行の値は、その行が新しい行として挿入された場合は 1、既存の行が更新された場合は 2、既存の行がその現在の値に設定された場合は 0 です。mysqld への接続時に CLIENT_FOUND_ROWS フラグを mysql_real_connect() に指定すると、既存の行がその現在の値に設定された場合の影響を受けた行の値は (0 ではなく) 1 になります。セクション13.2.5.3「INSERT ... ON DUPLICATE KEY UPDATE 構文」を参照してください。

テーブルに挿入するには、そのテーブルに対する INSERT 権限が必要です。ON DUPLICATE KEY UPDATE 句が使用されていて、重複キーのために代わりに UPDATE が実行される場合、このステートメントには、更新されるカラムに対する UPDATE 権限が必要です。読み取られるが、変更されないカラムの場合は、SELECT 権限のみが必要です (ON DUPLICATE KEY UPDATE 句にある col_name=expr 割り当ての右側でのみ参照されるカラムの場合など)。

MySQL 5.6.6 より前は、テーブルレベルのロックを採用した MyISAM などのストレージエンジンを使用しているパーティション化されたテーブルに影響を与える INSERT によって、そのテーブルのすべてのパーティションがロックされました。これは、INSERT ... PARTITION ステートメントにも当てはまりました。(これは、行レベルロックを採用した InnoDB などのストレージエンジンでは発生しておらず、現在も発生しません。)MySQL 5.6.6 以降では、MySQL はパーティションロックプルーニングを使用します。これにより、行が挿入されるパーティションだけが実際にロックされるようになります。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。


User Comments
  Posted by Yakov on July 8, 2003
To insert special characters, like the "apostrophe" read the section on string syntax: http://www.mysql.com/doc/en/String_syntax.html

Here's an example:
insert into Citylist (cityname) VALUES ('St. John\'s')

  Posted by Csongor Fagyal on September 8, 2004
Please note: "INSERT... ON DUPLICATE KEY UPDATE..." can also use a compound (unique) key to check for duplication. This is very userful. For example:
If you have a log table to log hits to different websites daily, with "site_id"-s and "time" fields, where neither of them are primary keys, but togethether they are unique, then you can create a key on them, and then use "...ON DUPLICATE KEY..."

Table logs:
id: INT(11) auto_increment primary key
site_id: INT(11)
time: DATE
hits: INT(11)

Then:
CREATE UNIQUE INDEX comp ON logs (`site_id`, `time`);

And then you can:
INSERT INTO logs (`site_id`, `time`,`hits`) VALUES (1,"2004-08-09", 15) ON DUPLICATE KEY UPDATE hits=hits+15;

Excellent feature, and it is much faster and briefer then using first a select, then issuing either an update or an insert depending on the value of the select. You also get rid of the probably necessary table-lock during this action.
  Posted by Jason McManus on October 16, 2004
When using the INSERT ... ON DUPLICATE KEY UPDATE statement, the returned value is as follows:

1 for each successful INSERT.
2 for each successful UPDATE.

For example, if you insert 5 rows with this syntax, and 3 of them were inserted while 2 were updated, the return value would be 7:
((3 inserts * 1) + (2 updates * 2)) = 7.

The return value may at first appear worrisome, as only 5 rows in the table were actually modified, but actually provides more information, because you can determine the quantities of each query type performed from the return value.

For further information, see:
http://bugs.mysql.com/bug.php?id=2709
  Posted by Dark Lady on January 10, 2005
Fusion des fiches / How to make a file fusion and save it in a new table?

Le code suivant permet de créer une nouvelle table appelée "fusion" avec les champs partition en, classe, segment, F tot, F loc et indice specif.

CREATE TABLE `fusion` (
`partition en` VARCHAR( 11 ) NOT NULL,
`classe` VARCHAR( 11 ) NOT NULL,
`segment` TEXT NOT NULL ,
`F tot` INT NOT NULL ,
`F loc` INT NOT NULL ,
`indice specif` INT NOT NULL
);

On peut mettre à la suite de ce code, le code suivant autant de fois que voulu qui permet de fusionner les tables dans la nouvelle table "fusion":

INSERT INTO l4stal13prema00.`fusion` ( `partition en` ,
`classe` ,
`segment` ,
`F tot` ,
`F loc` ,
`indice specif` )
SELECT *
FROM f3p1
WHERE 1;

INSERT INTO l4stal13prema00.`fusion` ( `partition en` ,
`classe` ,
`segment` ,
`F tot` ,
`F loc` ,
`indice specif` )
SELECT *
FROM f3p2
WHERE 1;

  Posted by Dark Lady on January 10, 2005
Eviter les répétitions grâce à count(Segment)/ How to avoid REPETITIONS and save it in a new table with COUNT and INSERT ?

http://dev.mysql.com/doc/mysql/en/Counting_rows.html

If you know another way when inserting several files with almost the same data (cat dog turtle + cat dog parrot= cat dog turtle parrot) and avoid repetition, tell it please?

  Posted by Rolf Kleef on January 27, 2005
Perhaps it's good to add a reference in the part on the ON DUPLICATE KEY feature to the older REPLACE function, which does a DELETE+INSERT instead of an UPDATE in case of existing key/unique values.
  Posted by André Somplatzki on March 22, 2005
If you do an "INSERT ... ON DUPLICATE KEY UPDATE ..." and neither an insert is possible (because of duplicate keys) nor an update is necessary (because of identical values) you get "2 affected rows" anyway.
  Posted by Allen Morris on August 14, 2006
If you need plan to get the LAST_INSERT_ID() from a INSERT ... ON DUPLICATE KEY. use ``insert into ... on duplicate key id = LAST_INSERT_ID(id), ...;''

If you do this then SELECT LAST_INSERT_ID() will return either the inserted id or the updated id.
  Posted by Jun-Dai Bates-Kobashigawa on August 18, 2005
I haven't seen this mentioned elsewhere on this page, but you can use a SELECT statement as a single value if it returns a single value. For example, if we have two tables, t1 and t2:

CREATE TABLE t1 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b VARCHAR(10)) TYPE=InnoDB;

INSERT INTO t1 (b) VALUES ('Spike'), ('Chip'), ('John');

CREATE TABLE t2 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b INT NOT NULL,
FOREIGN KEY (b) REFERENCES t1 (a),
c VARCHAR(15)) TYPE=InnoDB;

We can INSERT rows into t2 that populate the foreign key column based on a SELECT statement on t1:

INSERT INTO t2 (b, c)
VALUES ((SELECT a FROM t1 WHERE b='Chip'), 'shoulder'),
((SELECT a FROM t1 WHERE b='Chip'), 'old block'),
((SELECT a FROM t1 WHERE b='John'), 'toilet'),
((SELECT a FROM t1 WHERE b='John'), 'long,silver'),
((SELECT a FROM t1 WHERE b='John'), 'li''l');

Then we get:
mysql> SELECT * FROM t2;
+---+---+-------------+
| a | b | c |
+---+---+-------------+
| 1 | 2 | shoulder |
| 2 | 2 | old block |
| 3 | 3 | toilet |
| 4 | 3 | long,silver |
| 5 | 3 | li'l |
+---+---+-------------+
5 rows in set (0.00 sec)

This is especially useful if you don't want to specify the ids for your rows (because they may differ from database to database, due to their being based on AUTO_INCREMENTs), but you want to refer to the values of other tables.

I haven't tested this to determine the version of MySQL this was introduced into, or whether it is necessary that the tables be InnoDB, but it works on my boxes (MySQL 4.1.12)
  Posted by Laurent Sarlette on April 28, 2006
If you want to add to a tableA a column existing in a tableB:

1) Create an empty column in the tableA:

ALTER TABLE tableA ADD color CHAR(20);

2) If you don't have an auto-incrementation in the two tables (tableB for exemple):

ALTER TABLE tableB ADD (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id));

3) Fill the columns with the values:

UPDATE tableA,tableB SET tableA.color=tableB.color WHERE tableA.id=tableB.id;
  Posted by Jan Jędrzejczyk on October 10, 2006
If you want to combine insert..select with setting an explicit value for a column - you can use join:

INSERT INTO TargetTable (col1, col2, col3)
SELECT col1,col2,col3
FROM SourceTable JOIN (SELECT 'ExplicitValue' AS col3) AS AnyAlias

This looks quite simple but it took me several hours to understand that there's no need for a special statement to handle such cases.

Regards!
  Posted by Diego d'Ippolito on December 15, 2006
To Jan Jędrzejczyk:

> INSERT INTO TargetTable (col1, col2, col3)
> SELECT col1,col2,col3
> FROM SourceTable
> JOIN (SELECT 'ExplicitValue' AS col3) AS AnyAlias

You could easily do the same thing just by using:

INSERT INTO TargetTable (col1, col2, col3)
SELECT col1,col2, 'ExplicitValue'
FROM SourceTable

hth,

Lokar
  Posted by Justin Sheckler on July 11, 2007
I've just discovered that the UPDATE part of the INSERT ... ON DUPLICATE KEY UPDATE syntax doesn't replicate to my slave servers. REPLACE works OK and is only slightly slower. This is with server version 5.0.36sp1-enterprise-gpl-log. I wouldn't recommend anyone use the INSERT ... UPDATE syntax in a replication environment.
  Posted by aleem latif on July 30, 2008
If you want to INSERT multiple records using single statement in MS SQL Server, then the syntax for MySQL wont work. But you can use this insert command to accomplish the same:

INSERT INTO tbl_test (FirstName)
SELECT 'Aleem'
UNION ALL
SELECT 'Latif'
UNION ALL
SELECT 'Mughal'
  Posted by Allan Kelly on March 11, 2010
With PHP, I use affected_rows to detect the success of an INSERT IGNORE. This is useful if you need to know whether an INSERT occurred, and is necessary because IGNORE suppresses errors.

PHP code below outputs:
r==1
r==0

Cheers, al.


<?php
$myi 
= new mysqli("localhost""user""pass""dbname");
$myi->query( <<<SQL_CREATE
create temporary table test_warnings
    (
    `id_` int(11) NOT NULL,
    `num_` int(11) default NULL,
    PRIMARY KEY (`id_`)
    );
SQL_CREATE
);
$sth=$myi->prepare("insert ignore into test_warnings (id_, num_) values (?,?)");
$id 9;
$num 1;
for( 
$i=0$i<2$i++ )
{
    
$sth->bind_param"ii"$id$num );
    
$sth->execute();
    
$r $myi->affected_rows;
    print 
"r==$r\n<br>";
    
$sth->reset;
}
$sth->close();
?> 


  Posted by Randy Amos on March 5, 2010
I love the examples here from the community, here's what I used to add some recovered backup records to an existing prod table (making sure the old records in prod were deleted first!):

INSERT INTO prod_table
(col1,
col2,
col3
)
SELECT * FROM bkup_table;

Hope this helps somebody.

  Posted by Devang Modi on August 30, 2011
Combine queries for Insert and Select always obeys Innodb locking rules
if one of the source table is based on Innodb engine.
It is also possible that the INSERT activity applicable to TEMPORARY
table which is not InnoDB engine. It is also possible that in SELECT
section with INNODB, some other TEMPORARY Tables are used.
Devang Modi
  Posted by Elliot Greene on September 28, 2011
I find this way is good to avoid Duplicated rows when inserting rows.

INSERT INTO users VALUES (userid='billgates', password='someword', name='Bill Gates', telephone='480-456-9344') ON DUPLICATE KEY UPDATE userid='billgates';

This record will not be inserted as the username is already in the database other fields can be used.

Regards,
Elliot
http://www.sioure.com
  Posted by William Ward on February 7, 2012
One major caveat may come up for those who wish to use NDB Cluster databases with Insert-Select statements and an auto-incrementing ID: if the auto-incrementing ID needs to be processed in serial order, you may have 32 row “holes” in the table that are backfilled out of order. This can be especially vexing if you are doing processing the data in near real-time and using the auto-incrementing IDs for a “High Water Mark”.

For example, take these tables:

CREATE TABLE t1 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b INT NOT NULL,
c VARCHAR(15)) ENGINE=ndbluster;

CREATE TABLE t2 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b INT NOT NULL,
c VARCHAR(15)) ENGINE=ndbluster;

And this psuedocode running on NDB node 3:
int b=0;
while (b<1000) {
INSERT INTO t1 (b,c) VALUES ($b,”Node 3”);
b++;
sleep(1);
}

This psuedocode running on NDB node 4:

int b=0;
while (b<1000) {
for (int x=0;x<10; x++) {
INSERT INTO t2(b,c) VALUES ($b,”Node 4”);
b++;
sleep(1);
}
INSERT INTO t1(b,c) SELECT (b,c) FROM t2;
DELETE FROM t2;
}

This will result in holes that are backfilled in t1. After a run, this would be the first 100 rows of
SELECT * FROM t1 ORDER BY a;
---------------------------------
| a	|b	|c		|
---------------------------------
|0	|0	|’Node 3’	|
|1 |1 |’Node 3’ |
|2 |2 |’Node 3’ |
|3 |3 |’Node 3’ |
|4 |4 |’Node 3’ |
|5 |5 |’Node 3’ |
|6 |6 |’Node 3’ |
|7 |7 |’Node 3’ |
|8 |8 |’Node 3’ |
|9 |9 |’Node 3’ |
|10 |0 |’Node 4’ |
|11 |1 |’Node 4’ |
| 12 |2 |’Node 4’ |
|13 |3 |’Node 4’ |
|14 |4 |’Node 4’ |
|15 |5 |’Node 4’ |
|16 |6 |’Node 4’ |
|17 |7 |’Node 4’ |
|18 |8 |’Node 4’ |
|19 |9 |’Node 4’ |
|20 |10 |’Node 4’ |
|21 |11 |’Node 4’ |
|22 |12 |’Node 4’ |
|23 |13 |’Node 4’ |
|24 |14 |’Node 4’ |
|25 |15 |’Node 4’ |
|26 |16 |’Node 4’ |
|27 |17 |’Node 4’ |
|28 |18 |’Node 4’ |
|29 |19 |’Node 4’ |
|30 |20 |’Node 4’ |
|31 |21 |’Node 4’ |
|32 |22 |’Node 4’ |
|33 |23 |’Node 4’ |
|34 |24 |’Node 4’ |
|35 |25 |’Node 4’ |
|36 |26 |’Node 4’ |
|37 |27 |’Node 4’ |
|38 |28 |’Node 4’ |
|39 |29 |’Node 4’ |
|40 |30 |’Node 4’ |
|41 |31 |’Node 4’ |
|42 |10 |’Node 3’ |
|43 |11 |’Node 3’ |
|44 |12 |’Node 3’ |
|45 |13 |’Node 3’ |
|46 |14 |’Node 3’ |
|47 |15 |’Node 3’ |
|48 |16 |’Node 3’ |
|49 |17 |’Node 3’ |
|50 |18 |’Node 3’ |
|51 |19 |’Node 3’ |
|52 |20 |’Node 3’ |
|53 |21 |’Node 3’ |
|54 |22 |’Node 3’ |
|55 |23 |’Node 3’ |
|56 |24 |’Node 3’ |
|57 |25 |’Node 3’ |
|58 |26 |’Node 3’ |
|59 |27 |’Node 3’ |
|60 |28 |’Node 3’ |
|61 |29 |’Node 3’ |
|62 |30 |’Node 3’ |
|63 |31 |’Node 3’ |
|64 |32 |’Node 3’ |
|65 |33 |’Node 3’ |
|66 |34 |’Node 3’ |
|67 |35 |’Node 3’ |
|68 |36 |’Node 3’ |
|69 |37 |’Node 3’ |
|70 |38 |’Node 3’ |
|71 |39 |’Node 3’ |
|72 |32 |’Node 4’ |
|73 |33 |’Node 4’ |
|74 |34 |’Node 4’ |
|75 |35 |’Node 4’ |
|76 |36 |’Node 4’ |
|77 |37 |’Node 4’ |
|78 |38 |’Node 4’ |
|79 |39 |’Node 4’ |
|80 |40 |’Node 4’ |
|81 |41 |’Node 4’ |
|82 |42 |’Node 4’ |
|83 |43 |’Node 4’ |
|84 |44 |’Node 4’ |
|85 |45 |’Node 4’ |
|86 |46 |’Node 4’ |
|87 |47 |’Node 4’ |
|88 |48 |’Node 4’ |
|89 |49 |’Node 4’ |
|90 |50 |’Node 4’ |
|91 |51 |’Node 4’ |
|92 |52 |’Node 4’ |
|93 |53 |’Node 4’ |
|94 |54 |’Node 4’ |
|95 |55 |’Node 4’ |
|96 |56 |’Node 4’ |
|97 |57 |’Node 4’ |
|98 |58 |’Node 4’ |
|99 |59 |’Node 4’ |


SELECT MAX(a) FROM t1;

will return 2008 as the highest “in use” a value, even though the table would have only 2000 actual results.

This has serious implications for using a as a High Water Mark; because node 4 “backfilled” t1 (node 3 jumped from inserting into a=9 to a=42 above, and from a=71 to a=104), the HWM will miss node4 values. This is a direct result of behavior modified for bug 31956:

ndb_autoincrement_prefetch_sz to specify prefetch between statements, changed default to1 (with internal prefetch to at least 32 inside a statement), added handling of updates of pk/unique key with auto_increment

Becasue an Insert-Select does not know how many rows will be returned, 32 rows will be allocated, and will continue to be used until exhausted, regardless of if 10 rows at a time are moved, or 1 (if x had only been allowed to grow to 1, for example, a=1 would have had 'Node 4' while the second 'Node 3' row would have been a=33). Therefore, it is NOT recommended to use Insert-Select statements with Cluster databases if the auto-incrementing ID is meant to imply an absolute order on the timing of insertion into a table. The developer will need to explicitly pull out each row from t2 and insert them individually into t1 for the desired effect.

  Posted by Jon Vance on November 1, 2012
I have discovered something that can be VERY important if you don't know about it. When using INSERT IGNORE, insert triggers are STILL FIRED when a duplicate key constraint prevents new rows from being inserted.
  Posted by Ben Lin on March 12, 2014
A note about "Insert Ignore":

If one column of the unique key is null, then no duplicate-error is catch, and duplicate entry can be inserted.

For example, you have a unique key (`id`, `second`), but the `second` is null when inserted:

drop table if exists import_temp.test;
create table import_temp.test(
`id` int(11) NOT NULL,
`second` int DEFAULT NULL,
UNIQUE KEY `i` (`id`, `second`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into import_temp.test(id, second)
values(1, null);
insert ignore into import_temp.test(id, second)
values(1, null);

then you have 2 entries of (1, null) in the table, opposing to the unique key of (`id`, `second`).

  Posted by Nathan Neulinger on April 18, 2015
Should note that this warning about inserts and nulls

"For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types."

also appears to apply to a single row "replace into" query, which can be very confusing to debug when it appears to not obey the table constraints and just turns nulls/missing columns into empty strings. This can particularly be a problem if you have a unique constraint on one of those columns.
  Posted by James Jensen on July 28, 2015
In the first comment, @Yakov posted a now-outdated link to string syntax. Here is the current link FWIW:

http://dev.mysql.com/doc/refman/5.1/en/string-literals.html

Sign Up Login You must be logged in to post a comment.