How to get query that would recreate sql table in PHPMyAdmin

I have table on MySQL server and would like to get the SQL that would re-create the table.

How do I get the query to recreate the S开发者_如何学运维QL table?

---

MySQL supports SHOW CREATE TABLE to return the SQL that was used to create a table.

From their docs:

mysql> SHOW CREATE TABLE t;
CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM

This can be useful if you have just a connection to the DB, versus a CLI on the server. If you have a CLI, use mysqldump like liamgriffiths recommends.

---

mysqldump can do it - http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

You can use it like this:

mysqldump [databasename] [tablename] > mysqltablesql.sql

---

If you are using phpMyAdmin, select a table then click the 'Export' tab. One of the output types is SQL. Selecting this will generate a SQL script to create the table and insert all of the data that is currently in the table. Make sure under the dump data option to select 'Structure and Data'

---

mysqldump [OPTIONS] database [tables]>backup-file.sql

If you don't give any tables or use the --databases or --all-databases, the whole database(s) will be dumped.

You can get a list of the options your version of mysqldump supports by executing mysqldump --help.

Note that if you run mysqldump without --quick or --opt, mysqldump will load the whole result set into memory before dumping the result. This will probably be a problem if you are dumping a big database.

---

If you have access to phpMyAdmin, you can get this code through the Export tab on the table that you require.

Select SQL then make sure you export "Structure" ("CREATE table" code) and "Data" ("INSERT" code) with Export type set to "INSERT".

---

I'd go with @liamgriffiths proposal of mysqldump, but you could also try create table <new-table-name> like <old-table-name> followed by insert into <new-table-name> select * from <old-table-name>