如何克隆 MySQL 表、索引和数据?
mysqlmysqli database更新于 2024/1/24 15:33:00
要克隆 MySQL 表、索引和数据，我们可以使用"LIKE"。我有一个数据库"business"，其中创建了许多表。在这里我们可以检查所有表。
mysql> USE business; Database changed
让我们显示所有表 −
mysql> SHOW tables; +------------------------+ | Tables_in_business | +------------------------+ | addcolumntable | | autoincrement | | autoincrementtable | | bookindexes | | chardemo | | columnvaluenulldemo | | dateadddemo | | deletedemo | | deleterecord | | demo | | demo1 | | demoascii | | demoauto | | demobcrypt | | demoemptyandnull | | demoint | | demoonreplace | | demoschema | | distinctdemo | | duplicatebookindexes | | duplicatefound | | employeetable | | existsrowdemo | | findandreplacedemo | | foreigntable | | foreigntabledemo | | groupdemo | | groupdemo1 | | incasesensdemo | | int1demo | | intdemo | | latandlangdemo | | limitoffsetdemo | | milliseconddemo | | modifycolumnnamedemo | | modifydatatype | | moneydemo | | moviecollection | | mytable | | nonasciidemo | | nthrecorddemo | | nulldemo | | nullwithselect | | pasthistory | | presenthistory | | primarytable | | primarytable1 | | primarytabledemo | | sequencedemo | | smallintdemo | | spacecolumn | | student | | tblstudent | | tbluni | | textdemo | | texturl | | trailingandleadingdemo | | unsigneddemo | | varchardemo | | varchardemo1 | | varchardemo2 | | varcharurl | | whereconditon | +------------------------+ 63 rows in set (0.25 sec)
现在，我正在使用上述数据库中的表 STUDENT。查询如下 −
mysql> DESC student;
以下是输出 −
+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | Name | varchar(100) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.20 sec)
现在让我们检查一下学生表的记录:
SELECT * from student
Here is the output:
+------+------+ | id | Name | +------+------+ | 1 | John | | 2 | Bob | +------+------+ 2 rows in set (0.00 sec)
以下是查询；我们可以使用 LIKE 克隆索引和数据。查询如下 −
mysql> CREATE table cloneStudent like student; Query OK, 0 rows affected (1.57 sec) mysql> insert cloneStudent SELECT *from student; Query OK, 2 rows affected (0.60 sec) Records: 2 Duplicates: 0 Warnings: 0
因此，我已成功创建了克隆以及数据。现在，我们可以借助 DESC 命令来证明我们已经创建了索引和数据的克隆，如下所示 −
mysql> DESC cloneStudent;
以下是输出 −
+-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | Name | varchar(100) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
让我们检查一下该表是否被克隆。
mysql> SELECT * from cloneStudent;
输出结果如下:
+------+------+ | id | Name | +------+------+ | 1 | John | | 2 | Bob | +------+------+ 2 rows in set (0.00 sec)
