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

Question

我正在创建一个存储函数，该函数应该在表中插入新行。该表中还有一个唯一列。

如何检查一切是否顺利，是否确实插入了行？

我如何才能准确地检查是否找到此唯一列（例如-尝试添加重复值）？

Answer 1

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

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

use test
DROP 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 INT
BEGIN
  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.nametable\G
SELECT * FROM test.nametable;

这是正在运行的示例：

mysql> use test
Database changed
mysql> 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.nametable\G
*************************** 1. row ***************************
       Table: nametable
Create 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=latin1
1 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 test
DROP 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 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 $$
DELIMITER ;
SELECT InsertName('rolando');
SELECT InsertName('rolando');
SELECT InsertName('pamela');
SELECT InsertName('pamela');
SHOW CREATE TABLE test.nametable\G
SELECT * FROM test.nametable;

结果如下：

mysql> use test
Database changed
mysql> 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.nametable\G
*************************** 1. row ***************************
       Table: nametable
Create 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=latin1
1 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之前检查重复密钥。