MySQL Insert Select - NOT NULL fields

Question

Oh hey there,

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

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)

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:

Without sql_mode set:

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)

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)

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

Conclusion:

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

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

Thanks in advance,

K

Eugen Rieck · Accepted Answer · 2013-12-19 01:29:24Z

4

Simply use a WHERE clause:

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

answered Dec 19, 2013 at 1:29

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kbuilds Over a year ago

Also, is there an alternative method that would not require me to manually protect each of the columns where this could happen?

Eugen Rieck Over a year ago

The same oddities in the string/null handling, that produce the artifacts, can be used to filter them. MySQL: You love it, and you loathe it!

Eugen Rieck Over a year ago

The automatic mechanism is the SQL mode, which you already found.

kbuilds Over a year ago

Hmm, but it seems that the sql_mode setting doesn't affect the INSERT... SELECT. It seems to only affect individual inserts

Collectives™ on Stack Overflow

MySQL Insert Select - NOT NULL fields

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related