Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.2Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 26.2Mb
HTML Download (TGZ) - 6.6Mb
HTML Download (Zip) - 6.6Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 170.6Kb
Man Pages (Zip) - 279.0Kb
Info (Gzip) - 2.5Mb
Info (Zip) - 2.5Mb
Excerpts from this Manual

13.2.5 INSERT Syntax

    [INTO] tbl_name
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

    [INTO] tbl_name
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

    [INTO] tbl_name
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

    {expr | DEFAULT}

    value [, value] ...

    col_name = value

    assignment [, assignment] ...

INSERT inserts new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT with an ON DUPLICATE KEY UPDATE clause enables existing rows to be updated if a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY.

For additional information about INSERT ... SELECT and INSERT ... ON DUPLICATE KEY UPDATE, see Section, “INSERT ... SELECT Syntax”, and Section, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

Inserting into a table requires the INSERT privilege for the table. If the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes an UPDATE to be performed instead, the statement requires the UPDATE privilege for the columns to be updated. For columns that are read but not modified you need only the SELECT privilege (such as for a column referenced only on the right hand side of an col_name=expr assignment in an ON DUPLICATE KEY UPDATE clause).

You can use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows replace the old rows rather than being discarded. See Section 13.2.8, “REPLACE Syntax”.

tbl_name is the table into which rows should be inserted. Specify the columns for which the statement provides values as follows:

  • Provide a parenthesized list of comma-separated column names following the table name. In this case, a value for each named column must be provided by the VALUES list or the SELECT statement.

  • If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.

  • A SET clause indicates columns explicitly by name, together with the value to assign each one.

Column values can be given in several ways:

  • If strict SQL mode is not enabled, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 11.6, “Data Type Default Values”. See also Section, “Constraints on Invalid Data”.

    If strict SQL mode is enabled, an INSERT statement generates an error if it does not specify an explicit value for every column that has no default value. See Section 5.1.8, “Server SQL Modes”.

  • If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value:

    INSERT INTO tbl_name () VALUES();

    If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value.

  • Use the keyword DEFAULT to set a column explicitly to its default value. This makes it easier to write INSERT statements that assign values to all but a few columns, because it enables you to avoid writing an incomplete VALUES list that does not include a value for each column in the table. Otherwise, you must provide the list of column names corresponding to each value in the VALUES list.

  • In expressions, you can use DEFAULT(col_name) to produce the default value for column col_name.

  • Type conversion of an expression expr that provides a column value might occur if the expression data type does not match the column data type. Conversion of a given value can result in different inserted values depending on the column type. For example, inserting the string '1999.0e-2' into an INT, FLOAT, DECIMAL(10,6), or YEAR column inserts the value 1999, 19.9921, 19.992100, or 1999, respectively. The value stored in the INT and YEAR columns is 1999 because the string-to-number conversion looks only at as much of the initial part of the string as may be considered a valid integer or year. For the FLOAT and DECIMAL columns, the string-to-number conversion considers the entire string a valid numeric value.

  • An expression expr can refer to any column that was set earlier in a value list. For example, you can do this because the value for col2 refers to col1, which has previously been assigned:

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

    But the following is not legal, because the value for col1 refers to col2, which is assigned after col1:

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

    An exception occurs for columns that contain AUTO_INCREMENT values. Because AUTO_INCREMENT values are generated after other value assignments, any reference to an AUTO_INCREMENT column in the assignment returns a 0.

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of comma-separated column values, with lists enclosed within parentheses and separated by commas. Example:

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

Each values list must contain exactly as many values as are to be inserted per row. The following statement is invalid because it contains one list of nine values, rather than three lists of three values each:

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

VALUE is a synonym for VALUES in this context. Neither implies anything about the number of values lists, nor about the number of values per list. Either may be used whether there is a single values list or multiple lists, and regardless of the number of values per list.

The affected-rows value for an INSERT can be obtained using the ROW_COUNT() SQL function or the mysql_affected_rows() C API function. See Section 12.14, “Information Functions”, and Section, “mysql_affected_rows()”.

If you use an INSERT ... VALUES statement with multiple value lists or INSERT ... SELECT, the statement returns an information string in this format:

Records: N1 Duplicates: N2 Warnings: N3

If you are using the C API, the information string can be obtained by invoking the mysql_info() function. See Section, “mysql_info()”.

Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted because Duplicates can be nonzero.) Duplicates indicates the number of rows that could not be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

  • Inserting NULL into a column that has been declared NOT NULL. 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. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

  • Setting a numeric column to a value that lies outside the column's range. The value is clipped to the closest endpoint of the range.

  • Assigning a value such as '10.34 a' to a numeric column. The trailing nonnumeric text is stripped off and the remaining numeric part is inserted. If the string value has no leading numeric part, the column is set to 0.

  • Inserting a string into a string column (CHAR, VARCHAR, TEXT, or BLOB) that exceeds the column's maximum length. The value is truncated to the column's maximum length.

  • Inserting a value into a date or time column that is illegal for the data type. The column is set to the appropriate zero value for the type.

If INSERT inserts a row into a table that has an AUTO_INCREMENT column, you can find the value used for that column by using the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function.


These two functions do not always behave identically. The behavior of INSERT statements with respect to AUTO_INCREMENT columns is discussed further in Section 12.14, “Information Functions”, and Section, “mysql_insert_id()”.

The INSERT statement supports the following modifiers:

  • If you use the DELAYED modifier, the server puts the row or rows to be inserted into a buffer, and the client issuing the INSERT DELAYED statement can then continue immediately. If the table is in use, the server holds the rows. When the table is free, the server begins inserting rows, checking periodically to see whether there are any new read requests for the table. If there are, the delayed row queue is suspended until the table becomes free again. See Section, “INSERT DELAYED Syntax”.


    DELAYED is also disregarded for an INSERT that uses functions accessing tables or triggers, or that is called from a function or a trigger.

  • If you use the LOW_PRIORITY modifier, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting. It is possible, therefore, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time (or even forever) in a read-heavy environment. (This is in contrast to INSERT DELAYED, which lets the client continue at once.)

    LOW_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).


    LOW_PRIORITY should normally not be used with MyISAM tables because doing so disables concurrent inserts. See Section 8.11.3, “Concurrent Inserts”.

  • If you specify HIGH_PRIORITY, it overrides the effect of the --low-priority-updates option if the server was started with that option. It also causes concurrent inserts not to be used. See Section 8.11.3, “Concurrent Inserts”.

    HIGH_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).

  • If you use the IGNORE modifier, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

    IGNORE has a similar effect on inserts into partitioned tables where no partition matching a given value is found. Without IGNORE, such INSERT statements are aborted with an error. When INSERT IGNORE is used, the insert operation fails silently for rows containing the unmatched value, but inserts rows that are matched. For an example, see Section 19.2.2, “LIST Partitioning”.

    Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.

  • If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs. The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values. See Section, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

An INSERT statement affecting a partitioned table using a storage engine such as MyISAM that employs table-level locks locks all partitions of the table. This does not occur with tables using storage engines such as InnoDB that employ row-level locking. This issue is resolved in MySQL 5.6. For more information, see Section 19.5.4, “Partitioning and Table-Level Locking”.

User Comments
  Posted by Yakov on July 8, 2003
To insert special characters, like the "apostrophe" read the section on string syntax:

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)

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:
  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` )
FROM f3p1

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

  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 ?

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:


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


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):


3) Fill the columns with the values:

UPDATE tableA,tableB SET tableA.color=tableB.color WHERE;
  Posted by Jan Jędrzejczyk on October 10, 2006
If you want to combine 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.

  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


  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'
SELECT 'Latif'
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:

Cheers, al.

= 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_`)
$sth=$myi->prepare("insert ignore into test_warnings (id_, num_) values (?,?)");
$id 9;
$num 1;
$i=0$i<2$i++ )
$sth->bind_param"ii"$id$num );
$r $myi->affected_rows;

  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
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.

  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:

c VARCHAR(15)) ENGINE=ndbluster;

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”);

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”);
INSERT INTO t1(b,c) SELECT (b,c) FROM t2;

This will result in holes that are backfilled in t1. After a run, this would be the first 100 rows of
| 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’ |


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`)

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:

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