主页 / dba / 问题 / 18057
Accepted
jcho360
Asked: 2012-05-18 04:22:27 +0800 CST

在不使用备份的情况下复制 MySQL 中表的完整结构？

  • 772

在 MySQL 中，如何在不使用备份的情况下复制表结构？

       jcho360> show create table actor\G
*************************** 1. row *************************** 
           Table: actor
    Create Table: CREATE TABLE `actor` (
      `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
      `first_name` varchar(45) NOT NULL,
      `last_name` varchar(45) NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`actor_id`),
      KEY `idx_actor_last_name` (`last_name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    jcho360> create table actor_copy select * from actor;
    Query OK, 200 rows affected (0.07 sec)
    Records: 200  Duplicates: 0  Warnings: 0




 jcho360> show create table actor_copy\G
    *************************** 1. row ***************************
           Table: actor_copy
    Create Table: CREATE TABLE `actor_copy` (
      `actor_id` smallint(5) unsigned NOT NULL DEFAULT '0',
      `first_name` varchar(45) CHARACTER SET utf8 NOT NULL,
      `last_name` varchar(45) CHARACTER SET utf8 NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

当我做这样的事情时，我会失去结构，如 PK、FK、索引、ETC。

mysql backup

2 个回答

  1. Best Answer

    你应该能够做到：

    CREATE TABLE `actor_copy` LIKE `actor`;

    获取结构，完成索引。但是外键仍然需要手动创建。

    以上只会给你结构。然后，如果您愿意，您可以继续复制数据：

    INSERT INTO `actor_copy` SELECT * FROM `actor`;
    • 10

  2. @DTest 绝对正确地回答了这个问题。

    如果你想在定义中记录带有外键的表结构，这里有两种方法：（对于这个例子，我在数据库中创建了演员表test）

    技术#1：使用mysqldump

    MYSQL_CONN="-u... -p... -h..."
mysqldump ${MYSQL_CONN} -d test actor | sed 's/CREATE TABLE `actor`/CREATE TABLE `actor_copy`/' > actor.sql
TOP=`grep -n "CREATE TABLE" actor.sql | sed 's/:/ /' | awk '{print $1}'`
BOT=`grep -n "ENGINE=" actor.sql | sed 's/:/ /' | awk '{print $1}'`
(( DIFF = BOT - TOP + 1 ))
cat actor.sql | head -${BOT} | tail -${DIFF} > actor_copy.sql
cat actor_copy.sql

    结果如下：

    [redwards@lw-lts-155 lwdba]$ MYSQL_CONN="-u... -p... -h..."
[redwards@lw-lts-155 lwdba]$ mysqldump ${MYSQL_CONN} -d test actor | sed 's/CREATE TABLE `actor`/CREATE TABLE `actor_copy`/' > actor.sql
[redwards@lw-lts-155 lwdba]$ TOP=`grep -n "CREATE TABLE" actor.sql | sed 's/:/ /' | awk '{print $1}'`
[redwards@lw-lts-155 lwdba]$ BOT=`grep -n "ENGINE=" actor.sql | sed 's/:/ /' | awk '{print $1}'`
[redwards@lw-lts-155 lwdba]$ (( DIFF = BOT - TOP + 1 ))
[redwards@lw-lts-155 lwdba]$ cat actor.sql | head -${BOT} | tail -${DIFF} > actor_copy.sql
[redwards@lw-lts-155 lwdba]$ cat actor_copy.sql
CREATE TABLE `actor_copy` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

    技术#2：使用mysql客户端

    MYSQL_CONN="-u... -p... -h..."
mysql ${MYSQL_CONN} -Dtest -ANe"SHOW CREATE TABLE actor\G" > actor.sql
LINES=`wc -l < actor.sql`
(( LINES2 = LINES - 2 ))
tail -${LINES2} < actor.sql | sed 's/Create Table: //' | sed 's/`actor`/`actor_copy`/' > actor_copy.sql
echo ";" >> actor_copy.sql
cat actor_copy.sql

    结果如下：

    [redwards@lw-lts-155 lwdba]$ MYSQL_CONN="-u... -p... -h..."
[redwards@lw-lts-155 lwdba]$ mysql ${MYSQL_CONN} -Dtest -ANe"SHOW CREATE TABLE actor\G" > actor.sql
[redwards@lw-lts-155 lwdba]$ LINES=`wc -l < actor.sql`
[redwards@lw-lts-155 lwdba]$ (( LINES2 = LINES - 2 ))
[redwards@lw-lts-155 lwdba]$ tail -${LINES2} < actor.sql | sed 's/Create Table: //' | sed 's/`actor`/`actor_copy`/' > actor_copy.sql
[redwards@lw-lts-155 lwdba]$ echo ";" >> actor_copy.sql
[redwards@lw-lts-155 lwdba]$ cat actor_copy.sql
CREATE TABLE `actor_copy` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
;
    • 2

相关问题