Início / dba / Perguntas / 18057
Accepted
jcho360
Asked: 2012-05-18 04:22:27 +0800 CST

Copie a estrutura completa de uma tabela no MySQL sem usar um backup?

  • 772

No MySQL, como alguém pode copiar uma estrutura de tabela sem usar um backup?

       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)

quando faço algo assim perco a estrutura, tipo PK, FK, index , ETC.

mysql backup

2 respostas

  1. Best Answer

    Você deve ser capaz de fazer:

    CREATE TABLE `actor_copy` LIKE `actor`;

    para obter a estrutura, completa com índices. No entanto, as chaves estrangeiras ainda precisarão ser criadas manualmente.

    O que foi dito acima lhe dará apenas estrutura. Então você pode prosseguir para copiar os dados, se desejar:

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

  2. @DTest definitivamente respondeu a esta corretamente.

    Se você quiser gravar a estrutura da tabela com as chaves estrangeiras na definição, aqui estão duas maneiras de fazer isso: (Para este exemplo eu crio a tabela de atores no banco de dados test)

    TÉCNICA #1: usando 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

    E aqui está o resultado:

    [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;

    TÉCNICA #2: usando o cliente 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

    E aqui está o resultado:

    [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

relate perguntas