mysql语法支持如果数据存在则更新,不存在则插入。首先判断数据存在还是不存在的那个字段
要设置成unique索引
。
1.ON DUPLICATE KEY UPDATE
语法如下:
NSERT INTO 表名(唯一索引列, 列2, 列3) VALUE(值1, 值2, 值3) ON DUPLICATE KEY UPDATE 列=值, 列=值
举例,有表tb_addrbook如下:
mysql> show create table tb_addrbook;
+-------------+-------------------------------------------+
| Table | Create Table |
+-------------+-------------------------------------------+
| tb_addrbook | CREATE TABLE `tb_addrbook` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(11) NOT NULL DEFAULT '0',
`name` varchar(16) NOT NULL DEFAULT '',
`company` varchar(48) NOT NULL DEFAULT '',
`job` varchar(32) NOT NULL DEFAULT '',
`tel` varchar(16) NOT NULL DEFAULT '',
`mobile` varchar(11) NOT NULL DEFAULT '',
`mail` varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `num` (`num`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 |
+-------------+-------------------------------------------+
1 row in set (0.00 sec)
注意:表结构中唯一索引 UNIQUE KEY
num(
num) USING BTREE
现在查看表里面的数据:select * from tb_addrbook
mysql> select * from tb_addrbook;
+----+-----+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name | company | job | tel | mobile | mail |
+----+-----+--------+--------------+--------+--------------+-------------+-------------+
| 1 | 0 | 小张 | 小张科技 | 支援 | 010-12345678 | 13600000000 | 123@123.com |
+----+-----+--------+--------------+--------+--------------+-------------+-------------+
1 row in set (0.00 sec)
1.1 不存在则插入
INSERT INTO tb_addrbook(num,name,mobile) VALUE('1001','小李','13112345678') ON DUPLICATE KEY UPDATE name= '小李',mobile='13112345678'
mysql> select * from tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name | company | job | tel | mobile | mail |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| 1 | 0 | 小张 | 小张科技 | 支援 | 010-12345678 | 13600000000 | 123@123.com |
| 2 | 1001 | 小李 | | | | 13112345678 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
2 rows in set (0.00 sec)
1.2 存在则更新
INSERT INTO tb_addrbook(num,name,mobile) VALUE('1001','小李','18800000000') ON DUPLICATE KEY UPDATE name= '小李',mobile='18800000000'
mysql> select * from tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name | company | job | tel | mobile | mail |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| 1 | 0 | 小张 | 小张科技 | 支援 | 010-12345678 | 13600000000 | 123@123.com |
| 2 | 1001 | 小李 | | | | 18800000000 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
2 rows in set (0.00 sec)
1.3 存在则更新。insert部分 与 update部分 不同时,update中的部分生效。
INSERT INTO tb_addrbook(num,name,mobile) VALUE('1001','小李','18800000000') ON DUPLICATE KEY UPDATE name= '小李',mobile='1880'
mysql> select * from tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name | company | job | tel | mobile | mail |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| 1 | 0 | 小张 | 小张科技 | 支援 | 010-12345678 | 13600000000 | 123@123.com |
| 2 | 1001 | 小李 | | | | 1880 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
2 rows in set (0.00 sec)
1.4 INSERT部分,未指明唯一索引列;则更新第一行记录
INSERT INTO tb_addrbook(id, name, mobile) VALUES(3, '小王', '33333333333') ON DUPLICATE KEY UPDATE name='小王',mobile='12ile='1234'
mysql> SELECT * FROM tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name | company | job | tel | mobile | mail |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| 1 | 0 | 小王 | 小张科技 | 支援 | 010-12345678 | 1234 | 123@123.com |
| 2 | 1001 | 小李 | | | | 13112345678 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
特点:DUPLICATE不会删除原有的记录。即:不会破坏索引。
2.REPLACE INTO
REPLACE INTO 主要作用类似 INSERT 插入操作。主要的区别是 REPLACE INTO 会根据主键或者唯一索引检查数据是否存在,如果存在就先删除再更新。
语法:
REPLACE INTO 表名称(列1, 列2, 列3) VALUES(值1, 值2, 值3)
2.1 不存在则插入
mysql> REPLACE INTO tb_addrbook(num, name, mobile) VALUES(3000, '小山', '14412341234');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name | company | job | tel | mobile | mail |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| 1 | 0 | 小王 | 小张科技 | 支援 | 010-12345678 | 1234 | 123@123.com |
| 2 | 1001 | 小李 | | | | 13112345678 | |
| 3 | 3000 | 小山 | | | | 14412341234 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
注意:此时的主键id=3;
2.2 存在则先删除后插入
mysql> REPLACE INTO tb_addrbook(num, name, mobile) VALUES(3000, '小山', '14412341234');
Query OK, 2 rows affected (0.00 sec)
注意:上述语句导致2行数据受影响。是什么原因呢?
mysql> SELECT * FROM tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name | company | job | tel | mobile | mail |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| 1 | 0 | 小王 | 小张科技 | 支援 | 010-12345678 | 1234 | 123@123.com |
| 2 | 1001 | 小李 | | | | 13112345678 | |
| 4 | 3000 | 小山 | | | | 14412341234 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
3 rows in set (0.00 sec)
注意:此时的主键id=3的一条记录被删除,重新插入了一条主键id=4的新记录。
特点:
- REPLACE INTO底层是先删除后插入数据,会破坏索引、重新维护索引
- 必须要有主键或唯一索引才能有效,否则replace into就只新增了
3.REPLACE
replace是mysql 里面处理字符串比较常用的函数,可以替换字符串中的内容。类似的处理字符串的还有trim截取操作。
语法如下:
replace(object,search,replace)
3.1 查询结果替换
mysql> SELECT REPLACE('www.baidu.com', 'w', 'n');
+------------------------------------+
| REPLACE('www.baidu.com', 'w', 'n') |
+------------------------------------+
| nnn.baidu.com |
+------------------------------------+
1 row in set (0.00 sec)
3.2 更新数据
mysql> UPDATE tb_addrbook SET name=REPLACE(name, '小', '大');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name | company | job | tel | mobile | mail |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| 1 | 0 | 大王 | 小张科技 | 支援 | 010-12345678 | 1234 | 123@123.com |
| 2 | 1001 | 大李 | | | | 13112345678 | |
| 4 | 3000 | 大山 | | | | 14412341234 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
3 rows in set (0.00 sec)