如何检查INSERT在存储功能中是否运行良好？

如何检查INSERT在存储功能中是否运行良好？

您可以检查LAST_INSERT_ID（）函数和INSERT IGNORE。

如果INSERT IGNORE成功，则返回主键。让我们创建一个带有自动递增主键和名称上的唯一键的表。

use testDROp TABLE IF EXISTS nametable;CREATE TABLE nametable(  id int not null auto_increment,  name varchar(20) not null,  primary key (id),  unique key (name));DELIMITER $$DROP FUNCTION IF EXISTS `test`.`InsertName` $$CREATE FUNCTION `test`.`InsertName` (newname VARCHAr(20)) RETURNS INTBEGIN  INSERT IGNORE INTO test.nametable (name) VALUES (newname);  RETURN LAST_INSERT_ID();END $$DELIMITER ;SELECT InsertName('rolando');SELECT InsertName('rolando');SELECT InsertName('pamela');SELECT InsertName('pamela');SHOW CREATE TABLE test.nametableGSELECT * FROM test.nametable;

这是正在运行的示例：

mysql> use testDatabase changedmysql> DROp TABLE IF EXISTS nametable;Query OK, 0 rows affected (0.04 sec)mysql> CREATE TABLE nametable    -> (    ->   id int not null auto_increment,    ->   name varchar(20) not null,    ->   primary key (id),    ->   unique key (name)    -> );Query OK, 0 rows affected (0.07 sec)mysql> DELIMITER $$mysql> DROP FUNCTION IF EXISTS `test`.`InsertName` $$Query OK, 0 rows affected (0.00 sec)mysql> CREATE FUNCTION `test`.`InsertName` (newname VARCHAr(20)) RETURNS INT    -> BEGIN    ->   INSERT IGNORE INTO test.nametable (name) VALUES (newname);    ->   RETURN LAST_INSERT_ID();    -> END $$Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> SELECT InsertName('rolando');+-----------------------+| InsertName('rolando') |+-----------------------+|          1 |+-----------------------+1 row in set (0.03 sec)mysql> SELECT InsertName('rolando');+-----------------------+| InsertName('rolando') |+-----------------------+|          0 |+-----------------------+1 row in set (0.02 sec)mysql> SELECT InsertName('pamela');+----------------------+| InsertName('pamela') |+----------------------+|         3 |+----------------------+1 row in set (0.02 sec)mysql> SELECT InsertName('pamela');+----------------------+| InsertName('pamela') |+----------------------+|         0 |+----------------------+1 row in set (0.03 sec)mysql> SHOW CREATE TABLE test.nametableG*************************** 1. row ***************************       Table: nametableCreate Table: CREATE TABLE `nametable` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> SELECT * FROM test.nametable;+----+---------+| id | name    |+----+---------+|  3 | pamela  ||  1 | rolando |+----+---------+2 rows in set (0.00 sec)mysql>

如上例所示，您可以检查函数的返回值。返回值非零表示INSERT IGNORE运行良好。返回值为零表示键重复，而没有在mysqld中引入错误号。

这种方法的缺点是，由于在密钥重复的情况下插入INSERT IGNORE的尝试失败，因此您无法返回并使用ID 2和ID 4。

让我们尝试另一个示例，该示例使用INSERT而不使用LAST_INSERT_ID（）进行不同的存储函数设置：

use testDROp TABLE IF EXISTS nametable;CREATE TABLE nametable(  id int not null auto_increment,  name varchar(20) not null,  primary key (id),  unique key (name));DELIMITER $$DROP FUNCTION IF EXISTS `test`.`InsertName` $$CREATE FUNCTION `test`.`InsertName` (newname VARCHAr(20)) RETURNS INTBEGIN  DECLARE rv INT;  SELECT COUNT(1) INTO rv FROM test.nametable WHERe name = newname;  IF rv = 0 THEN    INSERT INTO test.nametable (name) VALUES (newname);  END IF;  RETURN rv;END $$DELIMITER ;SELECt InsertName('rolando');SELECT InsertName('rolando');SELECT InsertName('pamela');SELECT InsertName('pamela');SHOW CREATE TABLE test.nametableGSELECT * FROM test.nametable;

结果如下：

mysql> use testDatabase changedmysql> DROp TABLE IF EXISTS nametable;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE TABLE nametable    -> (    ->   id int not null auto_increment,    ->   name varchar(20) not null,    ->   primary key (id),    ->   unique key (name)    -> );Query OK, 0 rows affected (0.10 sec)mysql> DELIMITER $$mysql> DROP FUNCTION IF EXISTS `test`.`InsertName` $$Query OK, 0 rows affected (0.00 sec)mysql> CREATE FUNCTION `test`.`InsertName` (newname VARCHAr(20)) RETURNS INT    -> BEGIN    ->   DECLARE rv INT;    ->   SELECT COUNT(1) INTO rv FROM test.nametable WHERe name = newname;    ->   IF rv = 0 THEN    ->     INSERT INTO test.nametable (name) VALUES (newname);    ->   END IF;    ->   RETURN rv;    -> END $$Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> SELECt InsertName('rolando');+-----------------------+| InsertName('rolando') |+-----------------------+|          0 |+-----------------------+1 row in set (0.04 sec)mysql> SELECT InsertName('rolando');+-----------------------+| InsertName('rolando') |+-----------------------+|          1 |+-----------------------+1 row in set (0.00 sec)mysql> SELECT InsertName('pamela');+----------------------+| InsertName('pamela') |+----------------------+|         0 |+----------------------+1 row in set (0.03 sec)mysql> SELECT InsertName('pamela');+----------------------+| InsertName('pamela') |+----------------------+|         1 |+----------------------+1 row in set (0.00 sec)mysql> SHOW CREATE TABLE test.nametableG*************************** 1. row ***************************       Table: nametableCreate Table: CREATE TABLE `nametable` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> SELECT * FROM test.nametable;+----+---------+| id | name    |+----+---------+|  2 | pamela  ||  1 | rolando |+----+---------+2 rows in set (0.00 sec)mysql>

在此示例中，如果INSERT正常，则存储的函数将返回0，并在名称上带有重复键的情况下返回1。优势？不会浪费auto_increment的ID号。劣势？每次执行SELECT语句以检查表中已经存在的名称。

您可以选择要使用哪种方式处理重复键。第一种方法使mysqld处理INSERT
IGNORE的条件。第二种方法具有存储功能，该功能首先在INSERT之前检查重复密钥。