mysql 记录不存在就插入_MySQL：如果表中不存在则插入记录

我实际上并没有建议你这样做，因为Piskvor和其他人建议的 UNIQUE 索引是一个更好的方法，但你实际上可以做你正在尝试的事情：

CREATE TABLE `table_listnames` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(255) NOT NULL,

`address` varchar(255) NOT NULL,

`tele` varchar(255) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

插入记录：

INSERT INTO table_listnames (name, address, tele)

SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp

WHERE NOT EXISTS (

SELECT name FROM table_listnames WHERE name = 'Rupert'

) LIMIT 1;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+

| id | name | address | tele |

+----+--------+-----------+------+

| 1 | Rupert | Somewhere | 022 |

+----+--------+-----------+------+

尝试再次插入相同的记录：

INSERT INTO table_listnames (name, address, tele)

SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp

WHERE NOT EXISTS (

SELECT name FROM table_listnames WHERE name = 'Rupert'

) LIMIT 1;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

+----+--------+-----------+------+

| id | name | address | tele |

+----+--------+-----------+------+

| 1 | Rupert | Somewhere | 022 |

+----+--------+-----------+------+

插入不同的记录：

INSERT INTO table_listnames (name, address, tele)

SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp

WHERE NOT EXISTS (

SELECT name FROM table_listnames WHERE name = 'John'

) LIMIT 1;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+

| id | name | address | tele |

+----+--------+-----------+------+

| 1 | Rupert | Somewhere | 022 |

| 2 | John | Doe | 022 |

+----+--------+-----------+------+

等等...