Tabelas de Cópia MySQL

If we need to completely copy the MySQL data table, including the structure, index, default values, etc. If you only useCREATE TABLE ... SELECT command, is not feasible.

This chapter will introduce how to completely copy the MySQL data table, the steps are as follows:

• using SHOW CREATE TABLE command to obtain the creation of the data table (CREATE TABLE) statement, which includes the structure, index, and other information of the original data table.

• Copy the following command displayed SQL statement, modify the table name, and execute the SQL statement, through the above command To completely copy the data table structure.

• If you want to copy the content of the table, you can use INSERT INTO ... SELECT para implementar a declaração.

Online example

Try the following example to copy table w3codebox_tbl .

Step 1:

Get the complete structure of the data table.

mysql>　SHOW　CREATE　TABLE　w3codebox_tbl \G;
***************************　1. row　***************************
　　　　　　　Table:　w3codebox_tbl
Create Table:　CREATE　TABLE　`w3codebox_tbl`　(
　　`w3codebox_id`　int(11)　NOT　NULL　auto_increment,
　　`w3codebox_title`　varchar(100)　NOT　NULL　default　'',
　　`w3codebox_author`　varchar(40)　NOT　NULL　default　'',
　　`submission_date`　date　default　NULL,
　　PRIMARY　KEY　　(`w3codebox_id`),
　　UNIQUE　KEY　`AUTHOR_INDEX`　(`w3codebox_author`)
)　ENGINE=InnoDB　
1　row in set (0.00 sec)
ERROR:
No query specified

Step 2:

Modify the SQL statement table name and execute the SQL statement.

mysql>　CREATE　TABLE　`clone_tbl`　(
　　->　`w3codebox_id`　int(11)　NOT　NULL　auto_increment,
　　->　`w3codebox_title`　varchar(100)　NOT　NULL　default　'',
　　->　`w3codebox_author`　varchar(40)　NOT　NULL　default　'',
　　->　`submission_date`　date　default　NULL,
　　->　PRIMARY　KEY　　(`w3codebox_id`),
　　->　UNIQUE　KEY　`AUTHOR_INDEX`　(`w3codebox_author`)
->　)　ENGINE=InnoDB;
Query OK, 0 linhas afetadas (1.80 sec)

Passo 3:

Após a conclusão do segundo passo, você criará uma nova tabela clonada clone_tbl no banco de dados. Se você quiser copiar os dados da tabela, você pode usar INSERT INTO... SELECT para implementar a declaração.

mysql> INSERT INTO clone_tbl (w3codebox_id,
　　　　->　　　　　　　　　　　　　　　　　　　　　　　　w3> codebox_title,
　　　　->　　　　　　　　　　　　　　　　　　　　　　　　w3codebox_author,
　　　　->　　　　　　　　　　　　　　　　　　　　　　　　submission_date)
　　　　-> SELECT w3> codebox_id,w3> codebox_title,
　　　　->　　　　　　　　w3> codebox_author,submission_date
　　　　-> FROM w3codebox_tbl;
Query OK,　3　linhas afetadas (0.07　sec)
Registros:　3　　Duplicados: 0  Avisos: 0

Após a execução dos passos acima, será feita uma cópia completa do conteúdo da tabela, incluindo a estrutura da tabela e os dados da tabela.