MySQL INSERT 插入数据

INSERT VALUES 插入数据示例

创建表：

CREATE TABLE test_table (
    c1 varchar(100) not null,
    c2 varchar(100)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

插入一行数据，但只插入一列：

insert into test_table(c1) values('你好');

查看表中数据：

mysql> select * from test_table;
+------+--------+
| c1   | c2     |
+------+--------+
| 你好 | <null> |
+------+--------+

插入一行数据，插入两列：

insert into test_table(c1, c2) values('你好', '世界');

查看表中数据：

mysql> select * from test_table;
+------+--------+
| c1   | c2     |
+------+--------+
| 你好 | <null> |
| 你好 | 世界   |
+------+--------+

一次插入多行数据：

insert into test_table(c1, c2) values('你好', '中国'), ('你好', '北京');

mysql> select * from test_table;
+------+--------+
| c1   | c2     |
+------+--------+
| 你好 | <null> |
| 你好 | 世界   |
| 你好 | 中国   |
| 你好 | 北京   |
+------+--------+

注意，这里有一个坑：

insert into test_table(c1, c2) values(null, '中国'); -- 报错 (1048, u"Column 'c1' cannot be null")
insert into test_table(c1, c2) values(null, '中国'), ('你好', '上海'); -- 成功，第一条数据的c1列的值变成了默认的空字符串

INSERT SET 插入数据示例

创建表：

CREATE TABLE test_table (
    c1 varchar(100) not null,
    c2 varchar(100)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

插入多行：

insert into test_table
set 
    c1 = '你好',
    c2 = '中国'
;

查询数据：

mysql> select * from test_table;
+------+------+
| c1   | c2   |
+------+------+
| 你好 | 中国 |
+------+------+

INSERT IGNORE 插入数据示例

使用 insert ignore 插入数据时，若遇主键、唯一索引冲突，则不会插入数据，且不会报错。

示例1

create table `user_info` (
    `id` bigint unsigned not null auto_increment comment '自增ID',
    `name` varchar(45) not null default '' comment '用户名',
    primary key (`id`)
) engine = InnoDB character set = utf8mb4;

mysql> insert into user_info (id, name) values(1, 'xigua');
Query OK, 1 row affected

mysql> insert into user_info (id, name) values(1, 'xigua2');
(1062, u"Duplicate entry '1' for key 'PRIMARY'")

mysql > select * from user_info;
+----+--------+
| id | name   |
+----+--------+
| 1  | xigua |
+----+--------+

mysql> insert ignore into user_info (id, name) values(1, 'xigua2');
Query OK, 0 rows affected

mysql> select * from user_info;
+----+--------+
| id | name   |
+----+--------+
| 1  | xigua |
+----+--------+

mysql> insert ignore into user_info (id, name) values(2, 'xigua2');
Query OK, 1 row affected

mysql> select * from user_info;
+----+---------+
| id | name    |
+----+---------+
| 1  | xigua  |
| 2  | xigua2 |
+----+---------+

示例2

create table `user_info` (
    `id` bigint unsigned not null auto_increment comment '自增ID',
    `name` varchar(45) not null default '' comment '用户名',
    `note` varchar(1024) not null default '' comment '备注',
    primary key (`id`),
    unique key idx_name(name)
) engine = InnoDB character set = utf8mb4;

mysql> insert into user_info (name) values('xigua');

mysql> insert into user_info (name) values('xigua');
Query OK, 1 row affected

mysql> select * from user_info;
+----+--------+------+
| id | name   | note |
+----+--------+------+
| 1  | xigua |      |
+----+--------+------+

mysql> insert into user_info (name, note) values('xigua', '备注');
(1062, u"Duplicate entry 'xigua' for key 'idx_name'")

mysql> insert ignore into user_info (name, note) values('xigua', '备注');
Query OK, 0 rows affected