MySQL CREATE TABLE LIKE A1 as A2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10147565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CREATE TABLE LIKE A1 as A2
提问by Jigberto
I want to create a new table with properties of an old table and without duplicates. I want to do something like this:
我想创建一个具有旧表属性且没有重复项的新表。我想做这样的事情：
CREATE TABLE New_Users LIKE Old_Users,
AS
(SELECT * FROM Old_Users GROUP BY ID) ;
But the above is not working. Can anybody modify it to work?
但是上面的方法不起作用。任何人都可以修改它以工作吗？
回答by fancyPants
Your attempt wasn't that bad. You have to do it with LIKE, yes.
你的尝试并没有那么糟糕。你必须这样做LIKE，是的。
In the manualit says:
在手册中它说：
Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table.
使用 LIKE 根据另一个表的定义创建一个空表，包括原始表中定义的任何列属性和索引。
So you do:
所以你也是：
CREATE TABLE New_Users LIKE Old_Users;
Then you insert with
然后你插入
INSERT INTO New_Users SELECT * FROM Old_Users GROUP BY ID;
But you can not do it in one statement.
但是你不能在一个声明中做到这一点。
回答by Tim Hoolihan
Based on http://dev.mysql.com/doc/refman/5.0/en/create-table-select.html
基于http://dev.mysql.com/doc/refman/5.0/en/create-table-select.html
What about:
关于什么：
Create Table New_Users Select * from Old_Users Where 1=2;
and if that doesn't work, just select a row and truncate after creation:
如果这不起作用，只需选择一行并在创建后截断：
Create table New_Users select * from Old_Users Limit 1;
Truncate Table New_Users;
EDIT:
编辑：
I noticed your comment below about needing indexes, etc. Try:
我注意到您在下面关于需要索引等的评论。尝试：
show create table old_users;
#copy the output ddl statement into a text editor and change the table name to new_users
#run the new query
insert into new_users(id,name...) select id,name,... form old_users group by id;
That should do it. It appears that you are doing this to get rid of duplicates? In which case you may want to put a unique index on id. if it's a primary key, this should already be in place. You can either:
那应该这样做。看来您这样做是为了摆脱重复项？在这种情况下，您可能希望在 id 上放置一个唯一索引。如果它是一个主键，这应该已经到位。您可以：
#make primary key
alter table new_users add primary key (id);
#make unique
create unique index idx_new_users_id_uniq on new_users (id);
回答by Nesim Razon
For MySQL, you can do it like this:
对于 MySQL，你可以这样做：
CREATE TABLE New_Users SELECT * FROM Old_Users group by ID;
回答by Rahmad Saleh
CREATE TABLE new_table LIKE old_table;
CREATE TABLE new_table LIKE old_table;
or u can use this
或者你可以用这个
CREATE TABLE new_table as SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];
CREATE TABLE new_table as SELECT * FROM old_table WHERE 1 GROUP BY [要删除重复项的列];
回答by Brijesh Kumar Tripathi
You can use below query to create table and insert distinct values into this table:
您可以使用以下查询来创建表并将不同的值插入到该表中：
Select Distinct Column1, Column2, Column3 into New_Users from Old_Users
