mysql数据有更新,无插入
问题描述:
搜索到一个博主的操作,但是需要主键,我表中虽然设置了id为主键但是是自动递增的,所以插入的时候就写null,这样的话,怎么去使用这个语句?
答
mysql> show create table users \G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_idx1` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> insert into users (name) values('zhangsan');
Query OK, 1 row affected (0.02 sec)
mysql> insert into users (name) values('lisi');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+
| id | name |
+----+----------+
| 2 | lisi |
| 1 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)
mysql> insert into users (name) values('zhangsan') on duplicate key update name='wangwu';
Query OK, 2 rows affected (0.01 sec)
mysql> select * from users;
+----+--------+
| id | name |
+----+--------+
| 2 | lisi |
| 1 | wangwu |
+----+--------+
2 rows in set (0.00 sec)
mysql>
自增列在进行insert时,不需要在字段中列出: insert into 表名(字段1,字段2,这里不要列出自增列) values(...这里的值与前面的字段一一对应即可)