The Ultimate Guide to High Availability with MariaDB

INSERT

You are viewing an old version of this article. View the current version here.

Contents

  1. Syntax
  2. Duplicate Values
  3. Incompatibilities
  4. See also

Syntax

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

Or:

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

Or:

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

The INSERT statement is used to insert 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 ... SELECT is discussed further in the INSERT ... SELECT article.

The table name can be specified in the form db_name.tbl_name or, if a default database is selected, in the form tbl_name (see Identifier Qualifiers). This allows to use INSERT ... SELECT to copy rows between different databases.

MariaDB starting with 10.0

The PARTITION clause was introduced in MariaDB 10.0. It can be used in both the INSERT and the SELECT part. See Partition Pruning and Selection for details.

The columns list is optional. It specifies which values are explicitly inserted, and in which order. If this clause is not specified, all values must be explicitly specified, in the same order they are listed in the table definition.

The list of value follow the VALUES or VALUE keyword (which are interchangeable, regardless how much values you want to insert), and is wrapped by parenthesis. The values must be listed in the same order as the columns list. It is possible to specify more than one list to insert more than one rows with a single statement. If many rows are inserted, this is a speed optimization.

For one-row statements, the SET clause may be more simple, because you don't need to remember the columns order. All values are specified in the form col = expr.

Values can also be specified in the form of a SQL expression or subquery. However, the subquery cannot access the same table that is named in the INTO clause.

If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. If you use the HIGH_PRIORITY keyword, the statement has the same priority as SELECTs. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). However, if one of these keywords is specified, concurrent inserts cannot be used. See HIGH_PRIORITY and LOW_PRIORITY clauses for details.

For more details on the DELAYED option, see INSERT DELAYED.

Duplicate Values

By default, if you try to insert a duplicate row and there is a UNIQUE index, INSERT stops and an error like this is produced:

ERROR 1062 (23000): Duplicate entry 'dup_value' for key 'col'

To handle duplicates you can use the IGNORE clause, INSERT ON DUPLICATE KEY UPDATE or the REPLACE statement.

Incompatibilities

MariaDB until 5.5.28
  • MySQL and MariaDB before 5.5.28 didn't give warnings for duplicate key errors when using IGNORE. You can get the old behaviour if you set OLD_MODE to NO_DUP_KEY_WARNINGS_WITH_IGNORE

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.