pistou pistou - 1 year ago 104
MySQL Question

MySQL create / select adds fields from the source table

I encountered an issue with MySQL, adding fields from the selected source table.

Here is an example (you can find it on SQL Fiddle :

CREATE TABLE source_table (
id INT UNSIGNED NOT NULL,
foo VARCHAR(3),
INDEX (id)
);
INSERT INTO source_table (id, foo) VALUES (1, "one");

// Create another table and fill it from the source_table
CREATE TABLE example_table (
id INT UNSIGNED NOT NULL,
bar VARCHAR (3),
INDEX (id)
) SELECT
source_table.id,
source_table.foo
FROM source_table
WHERE source_table.id = 1;


In the end, my
example_table
will have a
foo
field, that I never requested to be create.
Plus,
bar
will be empty while
foo
will be filled.




The solution I found is to use aliases for each field, but it's redundant:

CREATE TABLE example_table (
id INT UNSIGNED NOT NULL,
bar VARCHAR (3),
INDEX (id)
) SELECT
source_table.id AS id,
source_table.foo AS bar
FROM source_table
WHERE source_table.id = 1;


Is there any trick in MySQL's configuration to avoid this behaviour? I feel like it would create surprising tables.

Answer Source

This is the documented behaviour of insert ... select ..., so there is no way to configure it to avoid this behaviour:

MySQL creates new columns for all elements in the SELECT. For example:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        ENGINE=MyISAM SELECT b,c FROM test2;

This creates a MyISAM table with three columns, a, b, and c.