mysql 复制表
把查询一张表的结果 复制到另外一张表
mysql> select host,user from mysql.user ; +-----------+------+ | host | user | +-----------+------+ | 127.0.0.1 | root | | ::1 | root | | localhost | | | localhost | root | | mysql | | | mysql | root | +-----------+------+ 6 rows in set (0.00 sec
把上面的查询结果复制 到另外一张表
创建一个数据库 db3 在里面创建一张复制表
mysql> create database db3 charset utf8; Query OK, 1 row affected (0.21 sec) mysql> use db3; Database changed
复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table t1 select host,user from mysql.user ; Query OK, 6 rows affected (0.28 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | host | char(60) | NO | | | | | user | char(16) | NO | | | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
只复制表结构
条件为假，查不到任何记录
查不到记录，但有表结构的
mysql> select host,user from mysql.user where 1=5; Empty set (0.00 sec)
mysql> create table t2 select host,user from mysql.user where 1=5; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | host | char(60) | NO | | | | | user | char(16) | NO | | | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from t2; Empty set (0.10 sec)
另外一张方法 只复制表结构
mysql> create table t3 like mysql.user;
mysql> desc t3; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | mysql_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 43 rows in set (0.24 sec)
