Skip to main content

Posts

Showing posts with the label insert

INSERT with NULL Values

In general, if a column has no default value, the effect of omitting it from the INSERT depends on whether it can take NULL values and SQL mode being used: 􀂃 If the column can take NULL values, it is set to NULL. 􀂃 If the column cannot take NULL values, it is set to the implicit default for the column data type if strict SQL mode is not enabled. If the strict mode is enabled, an error occurs. 􀂃 To get the next available number in an AUTO_INCREMENT column, leave the column out of the column list, or give it the value of NULL in the value list. The same applies to TIMESTAMP type columns.

INSERT with LAST_INSERT_ID

The MySQL-specific option LAST_INSERT_ID(expr) can be used after an INSERT to   after the insert takes place; mysql> INSERT INTO City (name, countrycode) -> VALUES ('Sarah City', 'USA'); Query OK, 1 row affected (#.## sec) mysql> SELECT LAST_INSERT_ID(); +---------------------------+ | LAST_INSERT_ID() | +----------------------------+ | 4080                            | +-----------------------------+ If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row  instead, LAST_INSERT_ID() is not meaningful. However, LAST_INSERT_ID(expr) can be used  to worked around this.