pistou pistou - 3 months ago 13
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

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.

Comments