您可以检查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为OK,则存储的函数返回0,并返回名称上带有重复键的1.优势? auto_increment没有浪费的id号.缺点?每次执行SELECT语句以检查表中已存在的名称.
您可以选择要处理重复键的方式.第一种方法让mysqld处理INSERT IGNORE的条件.第二种方法在INSERT之前首先检查重复键的存储函数.