insert 检查 mysql,mysql – 如何检查INSERT在存储函数中是否运行良好？

您可以检查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之前首先检查重复键的存储函数.