Mysql – Autoincrement primary key in CREATE TABLE … AS SELECT

auto-incrementctasMySQLprimary-keyselect

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query?

For example: 

create table `user_mv` select `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`

This query creates a table that contains firstname, lastname, lang, username, group_name columns. I want it to also have an id column that is an autoincrement primary key.

Is there any way to do this by changing this query?
I know I can do that by altering table after executing this query, but if there is any way to do this directly in the create table statement, I'd like to know how to do that.

Best Answer

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`;

As long as you don't have an 'id' column in your SELECT, this appears to do exactly what you want. The columns in the select will be appended to the right of the columns you declare.

http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html (also in previous versions).

Related Question