数据库SQL小技巧大揭秘-IGNORE选项让你的数据处置更冷静 (数据库sql server安装教程)
在中，IGNORE是一种在拔出或降级数据时处置抵触的选项。详细来说，在INSERT|UPDATE语句中，IGNORE的作用是在拔出或降级数据时疏忽特定的失误，而不造成整个操作失败。另外，IGNORE选项还可以在非空解放、写入的字段内容超越字段长度时启动截断处置等，上方是几个详细的例子。
一、主键或惟一键抵触
1、初始化测试表并初始化数据
mysql>createtabletest1(idintnotnullprimarykey,card_novarchar(10)notnull,namevarchar(20)notnull,c1varchar(2),uniquekeyuq_card_no(card_no));QueryOK,0rowsaffected(0.05sec)mysql>insertintotest1(id,card_no,name,c1)values(1,'1000000000','abc','a')QueryOK,1rowaffected(0.01sec)mysql>select*fromtest1;+----+------------+------+------+|id|card_no|name|c1|+----+------------+------+------+|1|1000000000|abc|a|+----+------------+------+------+1rowinset(0.00sec)2、主键抵触
拔出一个表中已存在的主键数据时，假设不参与ignore，则会报主键抵触。
mysql>insertintotest1(id,card_no,name,c1)values(1,'1000000001','abc','a');ERROR1062(23000):Duplicateentry'1'forkey'test1.PRIMARY'加上ignore选项后，结果如下：
mysql>select*fromtest1;+----+------------+------+------+|id|card_no|name|c1|+----+------------+------+------+|1|1000000000|abc|a|+----+------------+------+------+1rowinset(0.00sec)mysql>insertignoreintotest1(id,card_no,name,c1)values(1,'1000000001','abc','a'),->(2,'1000000001','ttt','b');QueryOK,1rowaffected,1warning(0.01sec)Records:2Duplicates:1Warnings:1mysql>showwarnings;+---------+------+---------------------------------------------+|Level|Code|Message|+---------+------+---------------------------------------------+|Warning|1062|Duplicateentry'1'forkey'test1.PRIMARY'|+---------+------+---------------------------------------------+1rowinset(0.00sec)mysql>select*fromtest1;+----+------------+------+------+|id|card_no|name|c1|+----+------------+------+------+|1|1000000000|abc|a||2|1000000001|ttt|b|+----+------------+------+------+2rowsinset(0.00sec)可以看到，有1条记载抵触，然而启动了warning揭示，而后继续启动其余无抵触项的处置。
假设需检查warning消息，可以经常使用showwarnings命令检查。
3、惟一键抵触
继续以上的表，先反常模式拔出一条惟一键已存在的记载。
mysql>select*fromtest1;+----+------------+------+------+|id|card_no|name|c1|+----+------------+------+------+|1|1000000000|abc|a||2|1000000001|ttt|b|+----+------------+------+------+2rowsinset(0.00sec)mysql>insertintotest1(id,card_no,name,c1)values(4,'1000000000','ccccc','a');ERROR1062(23000):Duplicateentry'1000000000'forkey'test1.uq_card_no'mysql>select*fromtest1;+----+------------+------+------+|id|card_no|name|c1|+----+------------+------+------+|1|1000000000|abc|a||2|1000000001|ttt|b|+----+------------+------+------+2rowsinset(0.00sec)可见，由于报错，数据未拔出。
上方经过参与ignore批量拔出数据。
mysql>select*fromtest1;+----+------------+------+------+|id|card_no|name|c1|+----+------------+------+------+|1|1000000000|abc|a||2|1000000001|ttt|b|+----+------------+------+------+2rowsinset(0.00sec)mysql>insertignoreintotest1(id,card_no,name,c1)values->(4,'1000000000','ccccc','a'),->(5,'1000000003','ccccabc','a');QueryOK,1rowaffected,1warning(0.00sec)Records:2Duplicates:1Warnings:1mysql>showwarnings;+---------+------+---------------------------------------------------------+|Level|Code|Message|+---------+------+---------------------------------------------------------+|Warning|1062|Duplicateentry'1000000000'forkey'test1.uq_card_no'|+---------+------+---------------------------------------------------------+1rowinset(0.00sec)mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||2|1000000001|ttt|b||5|1000000003|ccccabc|a|+----+------------+---------+------+3rowsinset(0.00sec)mysql>可见，和主键抵触相似，有抵触的数据将会疏忽告警而继续启动后续操作。
4、update操作
除了insert可以搭配ignore选项，update也可以参与ignore选项，例如：
降级主键：
mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||2|1000000001|ttt|b||5|1000000003|ccccabc|a|+----+------------+---------+------+3rowsinset(0.00sec)mysql>updatetest1setid=id+1;ERROR1062(23000):Duplicateentry'2'forkey'test1.PRIMARY'mysql>updateignoretest1setid=id+1;QueryOK,2rowsaffected,1warning(0.01sec)Rowsmatched:3Changed:2Warnings:1mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||3|1000000001|ttt|b||6|1000000003|ccccabc|a|+----+------------+---------+------+3rowsinset(0.00sec)mysql>降级惟一键：
mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||3|1000000001|ttt|b||6|1000000003|ccccabc|a|+----+------------+---------+------+3rowsinset(0.00sec)mysql>showwarnings;Emptyset(0.00sec)mysql>updatetest1setcard_no=card_no+1;ERROR1062(23000):Duplicateentry'1000000001'forkey'test1.uq_card_no'mysql>updateignoretest1setcard_no=card_no+1;QueryOK,2rowsaffected,1warning(0.02sec)Rowsmatched:3Changed:2Warnings:1mysql>showwarnings;+---------+------+---------------------------------------------------------+|Level|Code|Message|+---------+------+---------------------------------------------------------+|Warning|1062|Duplicateentry'1000000001'forkey'test1.uq_card_no'|+---------+------+---------------------------------------------------------+1rowinset(0.00sec)mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a|+----+------------+---------+------+3rowsinset(0.00sec)mysql>二、疏忽非空解放
1、列出字段赋值为null时
当列出需赋值的字段，然而对其中的非空字段赋值为null时，结果如下：
mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a|+----+------------+---------+------+3rowsinset(0.00sec)mysql>insertintotest1(id,card_no,name,c1)values->(7,'1000000005',null,'aa');ERROR1048(23000):Column'name'cannotbenullmysql>insertignoreintotest1(id,card_no,name,c1)values(7,'1000000005',null,'aa');QueryOK,1rowaffected,1warning(0.01sec)mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa|+----+------------+---------+------+4rowsinset(0.00sec)结果为：会拔出一个空字符串在表中而不会像反常SQL那样由于非空解放而失败。
有人纳闷，上方是空字符串么，验证结果如下：
mysql>select*fromtest1where;+----+------------+------+------+|id|card_no|name|c1|+----+------------+------+------+|7|1000000005||aa|+----+------------+------+------+1rowinset(0.00sec)mysql>select*fromtest1wherenameisnull;Emptyset(0.00sec)2、未列出字符串类型字段名
当赋值时未在字段列表中参与有非空解放的字符串类型的字段时，状况如下：
mysql>insertintotest1(id,name,c1)values(8,'aaa','aa');ERROR1364(HY000):Field'card_no'doesn'thaveadefaultvaluemysql>insertignoreintotest1(id,name,c1)values(8,'aaa','aa');QueryOK,1rowaffected,1warning(0.01sec)mysql>showwarnings;+---------+------+----------------------------------------------+|Level|Code|Message|+---------+------+----------------------------------------------+|Warning|1364|Field'card_no'doesn'thaveadefaultvalue|+---------+------+----------------------------------------------+1rowinset(0.00sec)mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa||8||aaa|aa|+----+------------+---------+------+5rowsinset(0.01sec)可见，字段未列出时，也可以拔出成功，也是将其拔出一个空字符串。
3、未列整型字段时
当赋值时未在字段列表中参与有非空解放的整型类型的字段时，状况如下：
mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa||8||aaa|aa|+----+------------+---------+------+5rowsinset(0.01sec)mysql>insertignoreintotest1(card_no,name,c1)values('1000000006','bbb','aa');QueryOK,1rowaffected,1warning(0.00sec)mysql>showwarnings;+---------+------+-----------------------------------------+|Level|Code|Message|+---------+------+-----------------------------------------+|Warning|1364|Field'id'doesn'thaveadefaultvalue|+---------+------+-----------------------------------------+1rowinset(0.00sec)mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|0|1000000006|bbb|aa||1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa||8||aaa|aa|+----+------------+---------+------+6rowsinset(0.00sec)mysql>结果：此时拔出了0（整型的自动值）。
三、字段超长
照旧启动在上述的测试表上启动测试
1、字符串超长
当字符串类型超长时，反常结果如下：
mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|0|1000000006|bbb|aa||1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa||8||aaa|aa|+----+------------+---------+------+6rowsinset(0.00sec)mysql>insertintotest1(id,card_no,name,c1)values(9,'1000000001','abc','a12345');ERROR1406(22001):>mysql>insertignoreintotest1(id,card_no,name,c1)values(9,'1000000001','abc','a12345');QueryOK,1rowaffected,1warning(0.01sec)mysql>showwarnings;+---------+------+-----------------------------------------+|Level|Code|Message|+---------+------+-----------------------------------------+|Warning|1265|/>结果：数据以截断的模式拔出成功了。
2、整型数据超长
当普通模式拔出一个超越int类型最大值的数据时，会间接因数据超越范围而报错。例如：
mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|0|1000000006|bbb|aa||1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa||8||aaa|aa||9|1000000001|abc|a1|+----+------------+---------+------+7rowsinset(0.00sec)mysql>insertintotest1(id,card_no,name,c1)values(999999999999999999999,'1000000003','abc','a2');ERROR1264(22003):Outofrangevalueforcolumn'id'atrow1mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|0|1000000006|bbb|aa||1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa||8||aaa|aa||9|1000000001|abc|a1|+----+------------+---------+------+7rowsinset(0.00sec)
而经常使用ignore选项后，可以拔出数据，例如：
mysql>select*fromtest1;+----+------------+---------+------+|id|card_no|name|c1|+----+------------+---------+------+|0|1000000006|bbb|aa||1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa||8||aaa|aa||9|1000000001|abc|a1|+----+------------+---------+------+7rowsinset(0.00sec)mysql>insertignoreintotest1(id,card_no,name,c1)values(999999999999999999999,'1000000003','abc','a2');QueryOK,1rowaffected,2warnings(0.01sec)mysql>showwarnings;+---------+------+---------------------------------------------+|Level|Code|Message|+---------+------+---------------------------------------------+|Warning|1264|Outofrangevalueforcolumn'id'atrow1||Warning|1264|Outofrangevalueforcolumn'id'atrow1|+---------+------+---------------------------------------------+2rowsinset(0.00sec)mysql>select*fromtest1;+------------+------------+---------+------+|id|card_no|name|c1|+------------+------------+---------+------+|0|1000000006|bbb|aa||1|1000000000|abc|a||3|1000000002|ttt|b||6|1000000004|ccccabc|a||7|1000000005||aa||8||aaa|aa||9|1000000001|abc|a1||2147483647|1000000003|abc|a2|+------------+------------+---------+------+8rowsinset(0.00sec)mysql>
结果：会以截断的模式拔出（int的最大值）
四、结语
总的来说，IGNORE提供了一种在拔出或降级时处置主键、惟一键抵触、非空解放字段未赋值、字段超长等意外时外部智能处置的方法，使得操作不由于某一行的抵触而终止，而是继续处置。但也由于其特点，会造成结果与预期不符的状况。在实践操作中还是倡导经常使用反常的模式启动处置，免得产生不用要的缺点。
php删除sql数据库的语句
MySQL为我们提供了delete和truncate语句来删除数据。
delete 语句的定义：
删除数据的时候用的大多都是 delete 语句。现在让我们来看一下 delete语句的定义。
DELETE[LOW_PRIORITY][QUICK][IGNORE]FROMtbl_name[WHEREwhere_definition][ORDER BY...][LIMITrow_count]
1
如何让sqlserver批量插入时忽略数据库中已经存在的数据，新数据正常插入？把唯一索引改成这样子的就可以了，记得把语句中的ignore删掉 |
免责声明：本文转载或采集自网络，版权归原作者所有。本网站刊发此文旨在传递更多信息，并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题，请联系本网，我们将在第一时间删除。同时，本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证，请读者仅作参考，并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失，本网站不承担任何责任。
