Create table with SELECT syntax in MySQL

Question

I would like to know how MySQL interprets the CREATE TABLE syntax:

If I write:

CREATE TABLE tbl1 (
    `v1` int,
    `v2` int
     CONSTRAINT idx PRIMARY KEY (v1)
)
SELECT a, b FROM tbl2;

Does it determine which value goes into v1 and which goes into v2 by their order in the select statement?
Does it use the names I designate in the CREATE TABLE statement or does it take them from the select statement?

I have used the CREATE TABLE XX SELECT val FROM YY before but would like to know more specifically about the above syntax.

informatik01 · Accepted Answer · 2021-07-16 23:17:04Z

12

With your current solution you'll get a table with the columns v1, v2, a, and b.

To see how it is done properly, see the "CREATE TABLE ... SELECT Statement" chapter in the official MySQL documentation.

So if you just want v1 and v2 with an index on v1, do it like this:

CREATE TABLE tbl1 (PRIMARY KEY (v1))
SELECT a v1,
       b v2
FROM tbl2;

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Aug 15, 2012 at 2:34

Michael Best

16.7k1 gold badge40 silver badges72 bronze badges

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

1 Answer 1