当前位置：首页 > news >正文

mysql关联表的复制

news 来源：原创 2024/4/29 0:48:13

1. 复制被参照的表：

　　　　CREATE TABLE clone_product_1 LIKE product_1;

　　　　INSERT INTO clone_product_1 SELECT * FROM product_1;

2. 复制参照表：

　　1. 获取数据表的完整结构。
　　2. 修改SQL语句的数据表名，并执行SQL语句。

CREATE TABLE `clone_product_attribute_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`variation_id` varchar(60) NOT NULL DEFAULT '',
`price` double NOT NULL,
`quantity` int(11) NOT NULL DEFAULT '0',
`reviews` int(11) NOT NULL DEFAULT '0',
`image` longtext NOT NULL,
`attributes` varchar(200) NOT NULL DEFAULT '{}',
`dictory` varchar(100) NOT NULL DEFAULT '',
`create_date` datetime NOT NULL,
`write_date` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `clone_product_attribute_1` (`product_id`),
CONSTRAINT `product_id_attribute_1` FOREIGN KEY (`product_id`) REFERENCES `clone_product_1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into clone_product_attribute_1 select * from product_attribute_1;

CREATE TABLE clone_product_26 LIKE product_26;   

INSERT INTO clone_product_26 SELECT * FROM product_26;  


CREATE TABLE `clone_product_attribute_26` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variation_id` varchar(60) NOT NULL DEFAULT '',
  `price` double NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT '0',
  `reviews` int(11) NOT NULL DEFAULT '0',
  `image` longtext NOT NULL,
  `attributes` varchar(200) NOT NULL DEFAULT '{}',
  `dictory` varchar(100) NOT NULL DEFAULT '',
  `create_date` datetime NOT NULL,
  `write_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `clone_product_attribute_26` (`product_id`),
  CONSTRAINT `clone_product_id_attribute_26` FOREIGN KEY (`product_id`) REFERENCES `clone_product_26` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into clone_product_attribute_26 select * from product_attribute_26;


CREATE TABLE `clone_product_detail_26` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `image` longtext NOT NULL,
  `description` longtext NOT NULL,
  `detail` longtext NOT NULL,
  `shipping_to` longtext NOT NULL,
  `feature` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `clone_product_detail_26` (`product_id`),
  CONSTRAINT `clone_product_id_detail_26` FOREIGN KEY (`product_id`) REFERENCES `clone_product_26` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3044 DEFAULT CHARSET=utf8;

insert into clone_product_detail_26 select * from product_detail_26;



TRUNCATE TABLE product_detail_26;
TRUNCATE TABLE product_attribute_26;

DELETE FROM product_26;

ALTER TABLE product_26 AUTO_INCREMENT = 1;