MySQL 插入选择 - NOT NULL 字段 [英] MySQL Insert Select - NOT NULL fields

问题描述

哦，嘿，

我正在尝试通过 INSERT... SELECT 语句将数据加载到表中，但我在 MySQL 处理 NULL 值时遇到了问题.

I am trying to load data into a table via a INSERT... SELECT statement, but I am having issues with MySQL handling NULL values.

在下面的示例中，table1 是源，table2 是目标(请注意，table2 对 description 字段有更多限制):

In the below example, table1 is the source and table2 is the destination (Note that table2 has more constraints on the description field):

mysql> drop table if exists table1;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists table2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists table1 (
    -> id int not null auto_increment,
    -> description varchar(45),
    -> primary key (`id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table if not exists table2 (
    -> id int not null auto_increment,
    -> description varchar(45) not null,
    -> primary key (`id`),
    -> unique index `unique_desc` (`description`)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert ignore into table1
    -> (description)
    -> values("stupid thing"),
    -> ("another thing"),
    -> (null),
    -> ("stupid thing"),
    -> ("last thing");
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+----+---------------+
| id | description   |
+----+---------------+
|  1 | stupid thing  |
|  2 | another thing |
|  3 | NULL          |
|  4 | stupid thing  |
|  5 | last thing    |
+----+---------------+
5 rows in set (0.00 sec)

mysql> insert ignore into table2
    -> (description)
    -> select description
    -> from table1;
Query OK, 4 rows affected, 1 warning (0.01 sec)
Records: 5  Duplicates: 1  Warnings: 1

mysql> select * from table2;
+----+---------------+
| id | description   |
+----+---------------+
|  3 |               |
|  2 | another thing |
|  4 | last thing    |
|  1 | stupid thing  |
+----+---------------+
4 rows in set (0.00 sec)

不应该有空格和 id=3 的行.我知道 MySQL 默认以这种方式处理 NOT NULL 指令，但我尝试将 sql_mode 选项指定为STRICT_ALL_TABLES"，我发现它具有以下影响:

The row with the empty space and id=3 should not be there. I understand that MySQL handles the NOT NULL directive this way by default, but I tried specifying the sql_mode option to "STRICT_ALL_TABLES", which I found to have the following affect:

没有设置sql_mode:

mysql> drop table if exists table2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists table2 (
    -> id int not null auto_increment,
    -> count int,
    -> description varchar(45) not null,
    -> primary key (`id`),
    -> unique index `unique_desc` (`description`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into table2
    -> (count,description)
    -> values(12,"stupid thing");
Query OK, 1 row affected (0.00 sec)

mysql> insert into table2
    -> (count)
    -> values(5);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from table2;
+----+-------+--------------+
| id | count | description  |
+----+-------+--------------+
|  1 |    12 | stupid thing |
|  2 |     5 |              |
+----+-------+--------------+
2 rows in set (0.00 sec)

将 sql_mode 设置为STRICT_ALL_TABLES":

With sql_mode set to "STRICT_ALL_TABLES":

mysql> drop table if exists table1;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> drop table if exists table2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists table2 (
    -> id int not null auto_increment,
    -> count int,
    -> description varchar(45) not null,
    -> primary key (`id`),
    -> unique index `unique_desc` (`description`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into table2
    -> (count,description)
    -> values(12,"stupid thing");
Query OK, 1 row affected (0.01 sec)

mysql> insert into table2
    -> (count)
    -> values(5);
ERROR 1364 (HY000): Field 'description' doesn't have a default value
mysql> select * from table2;
+----+-------+--------------+
| id | count | description  |
+----+-------+--------------+
|  1 |    12 | stupid thing |
+----+-------+--------------+
1 row in set (0.00 sec)

请注意，在上面的比较中，如果您明确地给 description 字段一个 NULL 值，数据库将正确地抱怨 WITH AND WITHOUT "STRICT_ALL_TABLES" 选项集:

Note that in the above comparison, if you explicitly give the description field a NULL value, the database will properly complain WITH AND WITHOUT the "STRICT_ALL_TABLES" option set:

mysql> insert into table2
    -> (count,description)
    -> values(12,null);
ERROR 1048 (23000): Column 'description' cannot be null

结论:

出于某种原因，设置 sql_mode 会影响这种插入，但不会影响 INSERT... SELECT 行为.

For some reason, setting the sql_mode affects this kind of insert, but does not affect the INSERT... SELECT behavior.

如何使用单个查询将 table1 中的数据获取到 table2 中，并且没有空单元格?

How can I get the data from table1 into table2 with a single query, and no empty cells?

提前致谢，

K

推荐答案

只需使用 WHERE 子句:

insert ignore into table2(description)
select description from table1
where description <> '' and description is not null

这篇关于MySQL 插入选择 - NOT NULL 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！