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.

Answer 1

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;