<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="insert">
      </a>
      15.2.7 INSERT Statement
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="insert-select.html">
       15.2.7.1 INSERT ... SELECT Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="insert-on-duplicate.html">
       15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="insert-delayed.html">
       15.2.7.3 INSERT DELAYED Statement
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045182522016">
  </a>
  <a class="indexterm" name="idm46045182520944">
  </a>
  <a class="indexterm" name="idm46045182519872">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43738595"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token punctuation">[</span><span class="token keyword">LOW_PRIORITY</span> <span class="token operator">|</span> <span class="token keyword">DELAYED</span> <span class="token operator">|</span> <span class="token keyword">HIGH_PRIORITY</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">IGNORE</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">INTO</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
    { {<span class="token keyword">VALUES</span> <span class="token operator">|</span> <span class="token keyword">VALUE</span>} <span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> }
    <span class="token punctuation">[</span><span class="token keyword">AS</span> <em class="replaceable">row_alias</em><span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">col_alias</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_alias</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> <em class="replaceable">assignment_list</em><span class="token punctuation">]</span>

<span class="token keyword">INSERT</span> <span class="token punctuation">[</span><span class="token keyword">LOW_PRIORITY</span> <span class="token operator">|</span> <span class="token keyword">DELAYED</span> <span class="token operator">|</span> <span class="token keyword">HIGH_PRIORITY</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">IGNORE</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">INTO</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
    <span class="token keyword">SET</span> <em class="replaceable">assignment_list</em>
    <span class="token punctuation">[</span><span class="token keyword">AS</span> <em class="replaceable">row_alias</em><span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">col_alias</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_alias</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> <em class="replaceable">assignment_list</em><span class="token punctuation">]</span>

<span class="token keyword">INSERT</span> <span class="token punctuation">[</span><span class="token keyword">LOW_PRIORITY</span> <span class="token operator">|</span> <span class="token keyword">HIGH_PRIORITY</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">IGNORE</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">INTO</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>
    { <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> 
      <span class="token operator">|</span> <span class="token keyword">TABLE</span> <span class="token keyword"><em class="replaceable">table_name</em></span> 
      <span class="token operator">|</span> <span class="token keyword">VALUES</span> <em class="replaceable">row_constructor_list</em>
    }
    <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> <em class="replaceable">assignment_list</em><span class="token punctuation">]</span>

<span class="token keyword"><em class="replaceable">value</em></span>:
    {<em class="replaceable">expr</em> <span class="token operator">|</span> <span class="token keyword">DEFAULT</span>}

<em class="replaceable">value_list</em>:
    <span class="token keyword"><em class="replaceable">value</em></span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">row_constructor_list</em>:
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>

<em class="replaceable">assignment</em>:
    <em class="replaceable">col_name</em> <span class="token operator">=</span> 
          <span class="token keyword"><em class="replaceable">value</em></span>
        <span class="token operator">|</span> <span class="token punctuation">[</span><em class="replaceable">row_alias</em><span class="token punctuation">.</span><span class="token punctuation">]</span><em class="replaceable">col_name</em>
        <span class="token operator">|</span> <span class="token punctuation">[</span><em class="replaceable">tbl_name</em><span class="token punctuation">.</span><span class="token punctuation">]</span><em class="replaceable">col_name</em>
        <span class="token operator">|</span> <span class="token punctuation">[</span><em class="replaceable">row_alias</em><span class="token punctuation">.</span><span class="token punctuation">]</span><em class="replaceable">col_alias</em>

<em class="replaceable">assignment_list</em>:
    <em class="replaceable">assignment</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">assignment</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   inserts new rows into an
      existing table. The
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
      ... VALUES
    </code>
   </a>
   ,
   <a class="link" href="values.html" title="15.2.19 VALUES Statement">
    <code class="literal">
     INSERT ... VALUES
      ROW()
    </code>
   </a>
   , and
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT ... SET
    </code>
   </a>
   forms of the statement insert rows based on explicitly specified
      values. The
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT
      ... SELECT
    </code>
   </a>
   form inserts rows selected from another table
      or tables. You can also use
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     INSERT ... TABLE
    </code>
   </a>
   to insert rows from a single table.
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   with an
   <code class="literal">
    ON
      DUPLICATE KEY UPDATE
   </code>
   clause enables existing rows to be
      updated if a row to be inserted would cause a duplicate value in a
   <code class="literal">
    UNIQUE
   </code>
   index or
   <code class="literal">
    PRIMARY KEY
   </code>
   .
      A row alias with one or more optional column aliases can be used
      with
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   to refer to the
      row to be inserted.
  </p>
  <p>
   For additional information about
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT ...
      SELECT
    </code>
   </a>
   and
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT ... ON
      DUPLICATE KEY UPDATE
    </code>
   </a>
   , see
   <a class="xref" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    Section 15.2.7.1, “INSERT ... SELECT Statement”
   </a>
   , and
   <a class="xref" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”
   </a>
   .
  </p>
  <p>
   In MySQL 8.4, the
   <code class="literal">
    DELAYED
   </code>
   keyword
      is accepted but ignored by the server. For the reasons for this,
      see
   <a class="xref" href="insert-delayed.html" title="15.2.7.3 INSERT DELAYED Statement">
    Section 15.2.7.3, “INSERT DELAYED Statement”
   </a>
   ,
  </p>
  <p>
   Inserting into a table requires the
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   privilege for the table. If
      the
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   clause is used and
      a duplicate key causes an
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   to
      be performed instead, the statement requires the
   <a class="link" href="privileges-provided.html#priv_update">
    <code class="literal">
     UPDATE
    </code>
   </a>
   privilege for the columns to
      be updated. For columns that are read but not modified you need
      only the
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   privilege (such as
      for a column referenced only on the right hand side of an
   <em class="replaceable">
    <code>
     col_name
    </code>
   </em>
   =
   <em class="replaceable">
    <code>
     expr
    </code>
   </em>
   assignment in an
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   clause).
  </p>
  <p>
   When inserting into a partitioned table, you can control which
      partitions and subpartitions accept new rows. The
   <code class="literal">
    PARTITION
   </code>
   clause takes a list of the
      comma-separated names of one or more partitions or subpartitions
      (or both) of the table. If any of the rows to be inserted by a
      given
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement do not match
      one of the partitions listed, the
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement fails with the
      error
   <span class="errortext">
    Found a row not matching the given partition
      set
   </span>
   . For more information and examples, see
   <a class="xref" href="partitioning-selection.html" title="26.5 Partition Selection">
    Section 26.5, “Partition Selection”
   </a>
   .
  </p>
  <p>
   <em class="replaceable">
    <code>
     tbl_name
    </code>
   </em>
   is the table into which rows
      should be inserted. Specify the columns for which the statement
      provides values as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      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
      <code class="literal">
       VALUES
      </code>
      list,
      <a class="link" href="values.html" title="15.2.19 VALUES Statement">
       <code class="literal">
        VALUES ROW()
       </code>
      </a>
      list, or
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement. For
          the
      <code class="literal">
       INSERT TABLE
      </code>
      form, the number of
          columns in the source table must match the number of columns
          to be inserted.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you do not specify a list of column names for
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT ...
          VALUES
       </code>
      </a>
      or
      <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
       <code class="literal">
        INSERT ...
          SELECT
       </code>
      </a>
      , values for every column in the table must be
          provided by the
      <code class="literal">
       VALUES
      </code>
      list,
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement, or
      <a class="link" href="table.html" title="15.2.16 TABLE Statement">
       <code class="literal">
        TABLE
       </code>
      </a>
      statement. If you do not
          know the order of the columns in the table, use
      <code class="literal">
       DESCRIBE
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
      to find out.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       SET
      </code>
      clause indicates columns explicitly
          by name, together with the value to assign each one.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Column values can be given in several ways:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      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
      <a class="xref" href="data-type-defaults.html" title="13.6 Data Type Default Values">
       Section 13.6, “Data Type Default Values”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045182442928">
     </a>
     <p>
      If strict SQL mode is enabled, an
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statement generates an
          error if it does not specify an explicit value for every
          column that has no default value. See
      <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
       Section 7.1.11, “Server SQL Modes”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If both the column list and the
      <code class="literal">
       VALUES
      </code>
      list
          are empty,
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      creates a row
          with each column set to its default value:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa87278878"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the keyword
      <code class="literal">
       DEFAULT
      </code>
      to set a column
          explicitly to its default value. This makes it easier to write
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statements that assign
          values to all but a few columns, because it enables you to
          avoid writing an incomplete
      <code class="literal">
       VALUES
      </code>
      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
      <code class="literal">
       VALUES
      </code>
      list.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a generated column is inserted into explicitly, the only
          permitted value is
      <code class="literal">
       DEFAULT
      </code>
      . For information
          about generated columns, see
      <a class="xref" href="create-table-generated-columns.html" title="15.1.20.8 CREATE TABLE and Generated Columns">
       Section 15.1.20.8, “CREATE TABLE and Generated Columns”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045182427488">
     </a>
    </li>
    <li class="listitem">
     <p>
      In expressions, you can use
      <a class="link" href="miscellaneous-functions.html#function_default">
       <code class="literal">
        DEFAULT(
        <em class="replaceable">
         <code>
          col_name
         </code>
        </em>
        )
       </code>
      </a>
      to produce the default value for column
      <em class="replaceable">
       <code>
        col_name
       </code>
      </em>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Type conversion of an expression
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      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
      <code class="literal">
       '1999.0e-2'
      </code>
      into an
      <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
       <code class="literal">
        INT
       </code>
      </a>
      ,
      <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
       <code class="literal">
        FLOAT
       </code>
      </a>
      ,
      <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
       <code class="literal">
        DECIMAL(10,6)
       </code>
      </a>
      , or
      <a class="link" href="year.html" title="13.2.4 The YEAR Type">
       <code class="literal">
        YEAR
       </code>
      </a>
      column inserts the value
      <code class="literal">
       1999
      </code>
      ,
      <code class="literal">
       19.9921
      </code>
      ,
      <code class="literal">
       19.992100
      </code>
      , or
      <code class="literal">
       1999
      </code>
      ,
          respectively. The value stored in the
      <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
       <code class="literal">
        INT
       </code>
      </a>
      and
      <a class="link" href="year.html" title="13.2.4 The YEAR Type">
       <code class="literal">
        YEAR
       </code>
      </a>
      columns is
      <code class="literal">
       1999
      </code>
      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
      <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
       <code class="literal">
        FLOAT
       </code>
      </a>
      and
      <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
       <code class="literal">
        DECIMAL
       </code>
      </a>
      columns, the
          string-to-number conversion considers the entire string a
          valid numeric value.
     </p>
    </li>
    <li class="listitem">
     <p>
      An expression
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      can refer to any
          column that was set earlier in a value list. For example, you
          can do this because the value for
      <code class="literal">
       col2
      </code>
      refers to
      <code class="literal">
       col1
      </code>
      , which has previously been
          assigned:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa48049138"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span>col1<span class="token punctuation">,</span>col2<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">,</span>col1<span class="token operator">*</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      But the following is not legal, because the value for
      <code class="literal">
       col1
      </code>
      refers to
      <code class="literal">
       col2
      </code>
      ,
          which is assigned after
      <code class="literal">
       col1
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa75007432"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span>col1<span class="token punctuation">,</span>col2<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span>col2<span class="token operator">*</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      An exception occurs for columns that contain
      <code class="literal">
       AUTO_INCREMENT
      </code>
      values. Because
      <code class="literal">
       AUTO_INCREMENT
      </code>
      values are generated after
          other value assignments, any reference to an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column in the assignment
          returns a
      <code class="literal">
       0
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements that use
   <code class="literal">
    VALUES
   </code>
   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:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99025408"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span>
    <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">8</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   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:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa63790687"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">,</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">8</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    VALUE
   </code>
   is a synonym for
   <code class="literal">
    VALUES
   </code>
   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.
  </p>
  <p>
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements using
   <a class="link" href="values.html" title="15.2.19 VALUES Statement">
    <code class="literal">
     VALUES ROW()
    </code>
   </a>
   syntax can also insert multiple rows. In this case, each value
      list must be contained within a
   <code class="literal">
    ROW()
   </code>
   (row
      constructor), like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75861128"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span>
    <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">8</span><span class="token punctuation">,</span><span class="token number">9</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The affected-rows value for an
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   can be obtained using the
   <a class="link" href="information-functions.html#function_row-count">
    <code class="literal">
     ROW_COUNT()
    </code>
   </a>
   SQL function or the
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-affected-rows.html" target="_top">
    <code class="literal">
     mysql_affected_rows()
    </code>
   </a>
   C API
      function. See
   <a class="xref" href="information-functions.html" title="14.15 Information Functions">
    Section 14.15, “Information Functions”
   </a>
   , and
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-affected-rows.html" target="_top">
    mysql_affected_rows()
   </a>
   .
  </p>
  <p>
   If you use
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT ...
      VALUES
    </code>
   </a>
   or
   <code class="literal">
    INSERT ... VALUES ROW()
   </code>
   with multiple value lists, or
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT ...
      SELECT
    </code>
   </a>
   or
   <code class="literal">
    INSERT ... TABLE
   </code>
   , the
      statement returns an information string in this format:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa56734347"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">Records: <em class="replaceable">N1</em> Duplicates: <em class="replaceable">N2</em> Warnings: <em class="replaceable">N3</em></code></pre>
  </div>
  <a class="indexterm" name="idm46045182369760">
  </a>
  <p>
   If you are using the C API, the information string can be obtained
      by invoking the
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-info.html" target="_top">
    <code class="literal">
     mysql_info()
    </code>
   </a>
   function. See
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-info.html" target="_top">
    mysql_info()
   </a>
   .
  </p>
  <p>
   <code class="literal">
    Records
   </code>
   indicates the number of rows processed
      by the statement. (This is not necessarily the number of rows
      actually inserted because
   <code class="literal">
    Duplicates
   </code>
   can be
      nonzero.)
   <code class="literal">
    Duplicates
   </code>
   indicates the number of
      rows that could not be inserted because they would duplicate some
      existing unique index value.
   <code class="literal">
    Warnings
   </code>
   indicates
      the number of attempts to insert column values that were
      problematic in some way. Warnings can occur under any of the
      following conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Inserting
      <code class="literal">
       NULL
      </code>
      into a column that has been
          declared
      <code class="literal">
       NOT NULL
      </code>
      . For multiple-row
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statements or
      <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
       <code class="literal">
        INSERT INTO ...
          SELECT
       </code>
      </a>
      statements, the column is set to the implicit
          default value for the column data type. This is
      <code class="literal">
       0
      </code>
      for numeric types, the empty string
          (
      <code class="literal">
       ''
      </code>
      ) for string types, and the
      <span class="quote">
       “
       <span class="quote">
        zero
       </span>
       ”
      </span>
      value for date and time types.
      <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
       <code class="literal">
        INSERT INTO ...
          SELECT
       </code>
      </a>
      statements are handled the same way as
          multiple-row inserts because the server does not examine the
          result set from the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      to
          see whether it returns a single row. (For a single-row
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      , no warning occurs when
      <code class="literal">
       NULL
      </code>
      is inserted into a
      <code class="literal">
       NOT
          NULL
      </code>
      column. Instead, the statement fails with an
          error.)
     </p>
    </li>
    <li class="listitem">
     <p>
      Setting a numeric column to a value that lies outside the
          column range. The value is clipped to the closest endpoint of
          the range.
     </p>
    </li>
    <li class="listitem">
     <p>
      Assigning a value such as
      <code class="literal">
       '10.34 a'
      </code>
      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
      <code class="literal">
       0
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Inserting a string into a string column
          (
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      ,
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      ,
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      , or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      ) that exceeds the column
          maximum length. The value is truncated to the column maximum
          length.
     </p>
    </li>
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      examples involving
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column values, see
      <a class="xref" href="example-auto-increment.html" title="5.6.9 Using AUTO_INCREMENT">
       Section 5.6.9, “Using AUTO_INCREMENT”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045182336576">
     </a>
     <a class="indexterm" name="idm46045182335504">
     </a>
     <p>
      If
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      inserts a row into a
          table that has an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column,
          you can find the value used for that column by using the
      <a class="link" href="information-functions.html#function_last-insert-id">
       <code class="literal">
        LAST_INSERT_ID()
       </code>
      </a>
      SQL function
          or the
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-insert-id.html" target="_top">
       <code class="literal">
        mysql_insert_id()
       </code>
      </a>
      C API
          function.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       These two functions do not always behave identically. The
            behavior of
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       statements
            with respect to
       <code class="literal">
        AUTO_INCREMENT
       </code>
       columns is
            discussed further in
       <a class="xref" href="information-functions.html" title="14.15 Information Functions">
        Section 14.15, “Information Functions”
       </a>
       , and
       <a class="ulink" href="/doc/c-api/8.4/en/mysql-insert-id.html" target="_top">
        mysql_insert_id()
       </a>
       .
      </p>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement supports the
      following modifiers:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If you use the
      <code class="literal">
       LOW_PRIORITY
      </code>
      modifier,
          execution of the
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      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
      <code class="literal">
       INSERT
          LOW_PRIORITY
      </code>
      statement is waiting. It is possible,
          therefore, for a client that issues an
      <code class="literal">
       INSERT
          LOW_PRIORITY
      </code>
      statement to wait for a very long time.
     </p>
     <a class="indexterm" name="idm46045182318576">
     </a>
     <p>
      <code class="literal">
       LOW_PRIORITY
      </code>
      affects only storage engines
          that use only table-level locking (such as
      <code class="literal">
       MyISAM
      </code>
      ,
      <code class="literal">
       MEMORY
      </code>
      , and
      <code class="literal">
       MERGE
      </code>
      ).
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       <code class="literal">
        LOW_PRIORITY
       </code>
       should normally not be used
            with
       <code class="literal">
        MyISAM
       </code>
       tables because doing so
            disables concurrent inserts. See
       <a class="xref" href="concurrent-inserts.html" title="10.11.3 Concurrent Inserts">
        Section 10.11.3, “Concurrent Inserts”
       </a>
       .
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      If you specify
      <code class="literal">
       HIGH_PRIORITY
      </code>
      , it overrides
          the effect of the
      <a class="link" href="server-system-variables.html#sysvar_low_priority_updates">
       <code class="option">
        --low-priority-updates
       </code>
      </a>
      option
          if the server was started with that option. It also causes
          concurrent inserts not to be used. See
      <a class="xref" href="concurrent-inserts.html" title="10.11.3 Concurrent Inserts">
       Section 10.11.3, “Concurrent Inserts”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045182307440">
     </a>
     <p>
      <code class="literal">
       HIGH_PRIORITY
      </code>
      affects only storage engines
          that use only table-level locking (such as
      <code class="literal">
       MyISAM
      </code>
      ,
      <code class="literal">
       MEMORY
      </code>
      , and
      <code class="literal">
       MERGE
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      If you use the
      <code class="literal">
       IGNORE
      </code>
      modifier, ignorable
          errors that occur while executing the
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statement are ignored.
          For example, without
      <code class="literal">
       IGNORE
      </code>
      , a row that
          duplicates an existing
      <code class="literal">
       UNIQUE
      </code>
      index or
      <code class="literal">
       PRIMARY KEY
      </code>
      value in the table causes a
          duplicate-key error and the statement is aborted. With
      <code class="literal">
       IGNORE
      </code>
      , the row is discarded and no error
          occurs. Ignored errors generate warnings instead.
     </p>
     <a class="indexterm" name="idm46045182296672">
     </a>
     <p>
      <a class="indexterm" name="idm46045182294928">
      </a>
      <code class="literal">
       IGNORE
      </code>
      has a similar effect on inserts into
          partitioned tables where no partition matching a given value
          is found. Without
      <code class="literal">
       IGNORE
      </code>
      , such
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statements are aborted
          with an error. When
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
          IGNORE
       </code>
      </a>
      is used, the insert operation fails silently
          for rows containing the unmatched value, but inserts rows that
          are matched. For an example, see
      <a class="xref" href="partitioning-list.html" title="26.2.2 LIST Partitioning">
       Section 26.2.2, “LIST Partitioning”
      </a>
      .
     </p>
     <p>
      Data conversions that would trigger errors abort the statement
          if
      <code class="literal">
       IGNORE
      </code>
      is not specified. With
      <code class="literal">
       IGNORE
      </code>
      , invalid values are adjusted to the
          closest values and inserted; warnings are produced but the
          statement does not abort. You can determine with the
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-info.html" target="_top">
       <code class="literal">
        mysql_info()
       </code>
      </a>
      C API function
          how many rows were actually inserted into the table.
     </p>
     <p>
      For more information, see
      <a class="xref" href="sql-mode.html#ignore-effect-on-execution" title="The Effect of IGNORE on Statement Execution">
       The Effect of IGNORE on Statement Execution
      </a>
      .
     </p>
     <p>
      You can use
      <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
       <code class="literal">
        REPLACE
       </code>
      </a>
      instead of
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      to overwrite old rows.
      <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
       <code class="literal">
        REPLACE
       </code>
      </a>
      is the counterpart to
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
          IGNORE
       </code>
      </a>
      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
      <a class="xref" href="replace.html" title="15.2.12 REPLACE Statement">
       Section 15.2.12, “REPLACE Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If you specify
      <code class="literal">
       ON DUPLICATE KEY UPDATE
      </code>
      , and
          a row is inserted that would cause a duplicate value in a
      <code class="literal">
       UNIQUE
      </code>
      index or
      <code class="literal">
       PRIMARY
          KEY
      </code>
      , an
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      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
      <code class="literal">
       CLIENT_FOUND_ROWS
      </code>
      flag to the
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-connect.html" target="_top">
       <code class="literal">
        mysql_real_connect()
       </code>
      </a>
      C API
          function when connecting to
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      , the
          affected-rows value is 1 (not 0) if an existing row is set to
          its current values. See
      <a class="xref" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
       Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045182269184">
     </a>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="insert-delayed.html" title="15.2.7.3 INSERT DELAYED Statement">
       <code class="literal">
        INSERT DELAYED
       </code>
      </a>
      was deprecated
          in MySQL 5.6, and is scheduled for eventual removal. In MySQL
          8.4, the
      <code class="literal">
       DELAYED
      </code>
      modifier is
          accepted but ignored. Use
      <code class="literal">
       INSERT
      </code>
      (without
      <code class="literal">
       DELAYED
      </code>
      ) instead. See
      <a class="xref" href="insert-delayed.html" title="15.2.7.3 INSERT DELAYED Statement">
       Section 15.2.7.3, “INSERT DELAYED Statement”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045182262720">
     </a>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
