mysql insert操作

insert的语法 INSERT [ LOW_PRIORITY | DELAYED | HIGH_PRIORITY ] [ IGNORE ] [ INTO ] tbl_name [ (col_name,...) ] VALUES ({expr | DEFAULT },...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 或： INSERT [ LOW_PRIORITY | DELAYED | H

insert的语法

<span>INSERT</span> <span>[</span><span>LOW_PRIORITY | DELAYED | HIGH_PRIORITY</span><span>]</span> <span>[</span><span>IGNORE</span><span>]</span>
    <span>[</span><span>INTO</span><span>]</span> tbl_name <span>[</span><span>(col_name,...)</span><span>]</span>
    <span>VALUES</span> ({expr <span>|</span> <span>DEFAULT</span><span>},...),(...),...
    </span><span>[</span><span> ON DUPLICATE KEY UPDATE col_name=expr, ... </span><span>]</span><span>
或：

</span><span>INSERT</span> <span>[</span><span>LOW_PRIORITY | DELAYED | HIGH_PRIORITY</span><span>]</span> <span>[</span><span>IGNORE</span><span>]</span>
    <span>[</span><span>INTO</span><span>]</span><span> tbl_name
    </span><span>SET</span> <span>col_name</span><span>=</span>{expr <span>|</span> <span>DEFAULT</span><span>}, ...
    </span><span>[</span><span> ON DUPLICATE KEY UPDATE col_name=expr, ... </span><span>]</span><span>
或：

</span><span>INSERT</span> <span>[</span><span>LOW_PRIORITY | HIGH_PRIORITY</span><span>]</span> <span>[</span><span>IGNORE</span><span>]</span>
    <span>[</span><span>INTO</span><span>]</span> tbl_name <span>[</span><span>(col_name,...)</span><span>]</span>
    <span>SELECT</span><span> ...
    </span><span>[</span><span> ON DUPLICATE KEY UPDATE col_name=expr, ... </span><span>]</span>

如果列清单和VALUES清单均为空清单，则INSERT会创建一个行，每个列都被设置为默认值：

 <span>INSERT</span> <span>INTO</span> tbl_name () <span>VALUES</span>();

假设worker表只有name和email,插入一条数据

<span>insert</span> <span>into</span> worker <span>values</span>(“tom”,”tom<span>@yahoo</span>.com”);

批量插入多条数据

<span>insert</span> <span>into</span> worker <span>values</span>(‘tom’,’tom<span>@yahoo</span>.com’),(‘paul’,’paul<span>@yahoo</span>.com’);

给出要赋值的那个列，然后再列出值的插入数据

<span>insert</span> <span>into</span> worker (name) <span>values</span><span> (‘tom’);
</span><span>insert</span> <span>into</span> worker (name) <span>values</span> (‘tom’), (‘paul’);

使用set插入数据

<span>insert</span> <span>into</span> worker <span>set</span> name<span>=</span>’tom’;

在 SET 子句中未命名的行都赋予一个缺省值，使用这种形式的 INSERT 语句不能插入多行。

 

一个expression可以引用在一个值表先前设置的任何列，例如

<span>INSERT</span> <span>INTO</span> tbl_name (col1,col2) <span>VALUES</span>(<span><strong>15</strong></span>,col1<span>*</span><span><strong>2</strong></span><span>);
</span><span>--</span><span>但不能这样</span>
<span>INSERT</span> <span>INTO</span> tbl_name (col1,col2) <span>VALUES</span>(col2<span>*</span><span><strong>2</strong></span>,<span><strong>15</strong></span>);

 

使用INSERT…SELECT语句插入从其他表选择的行

<span>insert</span> <span>into</span> tbl_name1(col1,col2) <span>select</span> col3,col4 <span>from</span><span> tbl_name2;
</span><span>--</span><span>如果每一列都有数据</span>
<span>insert</span> <span>into</span> tbl_name1 <span>select</span> col3,col4 <span>from</span> tbl_name2;

查询不能包含一个ORDER BY子句,而且INSERT语句的目的表不能出现在SELECT查询部分的FROM子句.

 

ON DUPLICATE KEY UPDATE

如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。

 

<span>--</span><span>假设a,b为唯一索引,表table没有1,2这样的行是正常插入数据，冲突时，更新c列的值</span>
<span>INSERT</span> <span>INTO</span> <span>table</span> (a,b,c) <span>VALUES</span> (<span><strong>1</strong></span>,<span><strong>2</strong></span>,<span><strong>3</strong></span>) <span>ON</span> DUPLICATE <span>KEY</span> <span>UPDATE</span> c<span>=</span><span><strong>3</strong></span><span>;
</span><span>--</span><span>或者是</span>
<span>INSERT</span> <span>INTO</span> <span>table</span> (a,b,c) <span>VALUES</span> (<span><strong>1</strong></span>,<span><strong>2</strong></span>,<span><strong>3</strong></span>) <span>ON</span> DUPLICATE <span>KEY</span> <span>UPDATE</span> c<span>=</span><span>values</span><span>(c);
</span><span>--</span><span>引用其他列更新冲突的行</span>
<span>INSERT</span> <span>INTO</span> <span>table</span> (a,b,c) <span>VALUES</span> (<span><strong>1</strong></span>,<span><strong>2</strong></span>,<span><strong>3</strong></span>),(<span><strong>4</strong></span>,<span><strong>5</strong></span>,<span><strong>6</strong></span>) <span>ON</span> DUPLICATE <span>KEY</span> <span>UPDATE</span> c<span>=</span><span>VALUES</span>(a)<span>+</span><span>VALUES</span>(b);

 向一个已定义为NOT NULL的列中插入NULL。对于一个多行INSERT语句或INSERT INTO...SELECT语句，根据列数据的类型，列被设置为隐含的默认值。对于数字类型，默认值为0；对于字符串类型，默认值为空字符串('')；对于日期和时间类型，默认值为“zero”值。

INSERT INTO...SELECT的ON DUPLICATE KEY UPDATE

<span>insert</span> <span>into</span><span> tbl_name1(a,b,c) 
  </span><span>select</span> col1,col2,col3 <span>from</span><span> tbl_name2 
</span><span>ON</span> DUPLICATE <span>KEY</span> <span>UPDATE</span> c<span>=</span><span>values</span>(c);

 

INSERT DELAYED

如果您的客户端不能等待INSERT完成，则这个选项是非常有用的，当一个客户端使用INSERT DELAYED时，会立刻从服务器处得到一个确定。并且行被排入队列，当表没有被其它线程使用时，此行被插入。

使用INSERT DELAYED的另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。

<span>INSERT</span> DELAYED <span>INTO</span> worker (name) <span>values</span> (‘tom’), (‘paul’);

使用DELAYED时有一些限制： 

• INSERT DELAYED仅适用于MyISAM, MEMORY和ARCHIVE表。对于MyISAM表，如果在数据文件的中间没有空闲的块，则支持同时采用SELECT和INSERT语句。在这些情况下，基本不需要对MyISAM使用INSERT DELAYED。
• INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED...SELECT语句的DELAYED和INSERT DELAYED...ON DUPLICATE UPDATE语句的DELAYED。
• 因为在行被插入前，语句立刻返回，所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。
• 对于SELECT语句，DELAYED行不可见，直到这些行确实被插入了为止。
• DELAYED在从属复制服务器中被忽略了，因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。