您可以检查 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 引入错误号。
此方法的缺点是您无法返回并使用 id 2 和 4,因为在出现重复键时尝试 INSERT IGNORE 失败。
让我们尝试另一个示例,使用 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 的条件。第二种方法在插入之前首先使用存储函数检查重复键。