创建 MySQL 查询以从现有表创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3634514/
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 MySQL query to create a table from an existing table
提问by Tristan
I have quite a large and complex table in a MySQL database that would be a pain to have to build a query for manually to recreate if anything went wrong.
我在 MySQL 数据库中有一个相当大且复杂的表，如果出现任何问题，必须手动构建查询以重新创建查询。
Is there someway I can get MySQL or some other tool/script I can use to get a Query made automatically that would recreate the structure of the table?
有什么办法可以让我得到 MySQL 或其他一些工具/脚本，我可以用它来自动生成一个查询，以重新创建表的结构？
Ideally if you suggested a tool/script it would be for Linux
理想情况下，如果您建议使用 Linux 的工具/脚本
回答by
SHOW CREATE TABLE `thetable`;
Also any backup method like mysqldumpshould give you an option to save structure as well as data.
此外，任何备份方法都mysqldump应该为您提供保存结构和数据的选项。
回答by Imre L
from http://dev.mysql.com/doc/refman/5.0/en/create-table.html
来自http://dev.mysql.com/doc/refman/5.0/en/create-table.html
to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:
根据另一个表的定义创建一个空表，包括原始表中定义的任何列属性和索引：
CREATE TABLE new_tbl LIKE orig_tbl;
回答by Sunny S.M
execute these queries :
执行这些查询：
If you want to copy table structure with primary key, foreign key and constrains with data...
如果要复制带有主键、外键和数据约束的表结构...
CREATE TABLE newtableName LIKE oldTableName;
INSERT newtableName SELECT * FROM oldTableName;
回答by pharalia
You can use the mysqldump tool to export the structure and data of a mysql table.
您可以使用 mysqldump 工具导出 mysql 表的结构和数据。
回答by Sohail Qureshi
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 =0
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 =0
回答by Vaibhav Gupta
you can export the structure of the table from phpmyadmin to a sql file...
您可以将表的结构从 phpmyadmin 导出到 sql 文件...
回答by aularon
SHOW CREATE TABLE `table_name`;
回答by PierrOz
I definitely agree with other folks:
with mysqldump you get a dump of your table (option --tables), let's say dumpMyTable.sql
then if you want to load this dump on a new schema:
我绝对同意其他人的看法：
使用 mysqldump 可以获得表的转储（选项 --tables），假设 dumpMyTable.sql
然后如果您想在新模式上加载此转储：
mysql -uuser_name -ppassword MyNewSchema < dumpMyTable.sql
if you want to load it in the same schema but in a new table you will have to edit the dumpMyTable.sql file and change the name of the table manually. If the dump file is very big, I recommend the use of the "sed" command
如果您想在相同的模式中加载它但在一个新表中，您将必须编辑 dumpMyTable.sql 文件并手动更改表的名称。如果转储文件很大，我推荐使用“sed”命令
回答by user434909
You may use SqlYog for this purpose. Solution is just 2 clicks away from this tool. You may use DataBase menu for this purpose. You can also copy only the schema or the whole data and both to even different servers.
为此，您可以使用 SqlYog。解决方案距离此工具仅 2 次点击。为此，您可以使用数据库菜单。您还可以仅将架构或整个数据以及两者都复制到不同的服务器。
