mysql - 纯SQL迁移表

Question

我有两组相同的表格。一种用于销售，一种用于分期销售。我决定将它们合并为一组表格会更好。

如何迁移分期预订餐 table 组？我对所有表使用自动递增键。我只是在寻找总体策略。

--
-- Table structure for table `phppos_sales`
--

CREATE TABLE IF NOT EXISTS `phppos_sales` (
  `sale_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `customer_id` int(10) DEFAULT NULL,
  `employee_id` int(10) NOT NULL DEFAULT '0',
  `comment` text COLLATE utf8_unicode_ci NOT NULL,
  `sale_id` int(10) NOT NULL AUTO_INCREMENT,
  `payment_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`),
  KEY `customer_id` (`customer_id`),
  KEY `employee_id` (`employee_id`),
  KEY `deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `phppos_sales`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_items`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_items` (
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `item_id` int(10) NOT NULL DEFAULT '0',
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `serialnumber` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `quantity_purchased` decimal(15,2) NOT NULL DEFAULT '0.00',
  `item_cost_price` decimal(15,2) NOT NULL,
  `item_unit_price` decimal(15,2) NOT NULL,
  `discount_percent` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_id`,`line`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_items`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_items_taxes`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_items_taxes` (
  `sale_id` int(10) NOT NULL,
  `item_id` int(10) NOT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `percent` decimal(15,3) NOT NULL,
  `cumulative` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_id`,`line`,`name`,`percent`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_items_taxes`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_item_kits`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_item_kits` (
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `item_kit_id` int(10) NOT NULL DEFAULT '0',
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `quantity_purchased` decimal(15,2) NOT NULL DEFAULT '0.00',
  `item_kit_cost_price` decimal(15,2) NOT NULL,
  `item_kit_unit_price` decimal(15,2) NOT NULL,
  `discount_percent` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_kit_id`,`line`),
  KEY `item_kit_id` (`item_kit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_item_kits`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_item_kits_taxes`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_item_kits_taxes` (
  `sale_id` int(10) NOT NULL,
  `item_kit_id` int(10) NOT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `percent` decimal(15,3) NOT NULL,
  `cumulative` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_kit_id`,`line`,`name`,`percent`),
  KEY `item_id` (`item_kit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_item_kits_taxes`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_payments`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_payments` (
  `sale_id` int(10) NOT NULL,
  `payment_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `payment_amount` decimal(15,2) NOT NULL,
  PRIMARY KEY (`sale_id`,`payment_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_payments`


--
-- Table structure for table `phppos_sales_suspended`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_suspended` (
  `sale_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `customer_id` int(10) DEFAULT NULL,
  `employee_id` int(10) NOT NULL DEFAULT '0',
  `comment` text COLLATE utf8_unicode_ci NOT NULL,
  `sale_id` int(10) NOT NULL AUTO_INCREMENT,
  `payment_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`),
  KEY `customer_id` (`customer_id`),
  KEY `employee_id` (`employee_id`),
  KEY `deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `phppos_sales_suspended`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_suspended_items`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_suspended_items` (
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `item_id` int(10) NOT NULL DEFAULT '0',
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `serialnumber` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `quantity_purchased` decimal(15,2) NOT NULL DEFAULT '0.00',
  `item_cost_price` decimal(15,2) NOT NULL,
  `item_unit_price` decimal(15,2) NOT NULL,
  `discount_percent` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_id`,`line`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_suspended_items`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_suspended_items_taxes`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_suspended_items_taxes` (
  `sale_id` int(10) NOT NULL,
  `item_id` int(10) NOT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `percent` decimal(15,3) NOT NULL,
  `cumulative` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_id`,`line`,`name`,`percent`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_suspended_items_taxes`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_suspended_item_kits`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_suspended_item_kits` (
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `item_kit_id` int(10) NOT NULL DEFAULT '0',
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `quantity_purchased` decimal(15,2) NOT NULL DEFAULT '0.00',
  `item_kit_cost_price` decimal(15,2) NOT NULL,
  `item_kit_unit_price` decimal(15,2) NOT NULL,
  `discount_percent` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_kit_id`,`line`),
  KEY `item_kit_id` (`item_kit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_suspended_item_kits`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_suspended_item_kits_taxes`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_suspended_item_kits_taxes` (
  `sale_id` int(10) NOT NULL,
  `item_kit_id` int(10) NOT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `percent` decimal(15,3) NOT NULL,
  `cumulative` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_kit_id`,`line`,`name`,`percent`),
  KEY `item_id` (`item_kit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_suspended_item_kits_taxes`
--


-- --------------------------------------------------------

--
-- Table structure for table `phppos_sales_suspended_payments`
--

CREATE TABLE IF NOT EXISTS `phppos_sales_suspended_payments` (
  `sale_id` int(10) NOT NULL,
  `payment_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `payment_amount` decimal(15,2) NOT NULL,
  PRIMARY KEY (`sale_id`,`payment_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `phppos_sales_suspended_payments`
--

迁移代码:

ALTER TABLE  `phppos_sales` ADD  `suspended_sale_id` INT( 10 ) NOT NULL;
ALTER TABLE  `phppos_sales` ADD  `suspended` INT( 1 ) NOT NULL  DEFAULT '0';
INSERT INTO `phppos_sales` (`sale_time`, `customer_id`, `employee_id`, `comment`, `payment_type`, `deleted`, `suspended_sale_id`, `suspended`)  (SELECT `sale_time`, `customer_id`, `employee_id`, `comment`, `payment_type`, `deleted`, `sale_id`, '1' FROM phppos_sales_suspended);
INSERT INTO `phppos_sales_items` (`sale_id`, `item_id`, `description`, `serialnumber`, `line`, `quantity_purchased`, `item_cost_price`, `item_unit_price`, `discount_percent`) (SELECT phppos_sales.sale_id,`item_id`, `description`, `serialnumber`, `line`, `quantity_purchased`, `item_cost_price`, `item_unit_price`, `discount_percent` FROM phppos_sales_suspended_items JOIN phppos_sales ON phppos_sales.suspended_sale_id = phppos_sales_suspended_items.sale_id );
INSERT INTO `phppos_sales_items_taxes` (`sale_id`, `item_id`, `line`, `name`, `percent`, `cumulative`) (SELECT phppos_sales.sale_id,`item_id`, `line`, `name`, `percent`, `cumulative` FROM phppos_sales_suspended_items_taxes JOIN phppos_sales ON phppos_sales.suspended_sale_id = phppos_sales_suspended_items_taxes.sale_id );
INSERT INTO `phppos_sales_item_kits` (`sale_id`, `item_kit_id`, `description`, `line`, `quantity_purchased`, `item_kit_cost_price`, `item_kit_unit_price`, `discount_percent`) (SELECT phppos_sales.sale_id, `item_kit_id`, `description`, `line`, `quantity_purchased`, `item_kit_cost_price`, `item_kit_unit_price`, `discount_percent` FROM phppos_sales_suspended_item_kits JOIN phppos_sales ON phppos_sales.suspended_sale_id = phppos_sales_suspended_item_kits.sale_id );
INSERT INTO `phppos_sales_item_kits_taxes` (`sale_id`, `item_kit_id`, `line`, `name`, `percent`, `cumulative`) (SELECT phppos_sales.sale_id,`item_kit_id`, `line`, `name`, `percent`, `cumulative` FROM phppos_sales_suspended_item_kits_taxes JOIN phppos_sales ON phppos_sales.suspended_sale_id = phppos_sales_suspended_item_kits_taxes.sale_id );
INSERT INTO `phppos_sales_payments` (`sale_id`, `payment_type`, `payment_amount`) (SELECT phppos_sales.sale_id, phppos_sales_suspended_payments.`payment_type`, `payment_amount` FROM phppos_sales_suspended_payments JOIN phppos_sales ON phppos_sales.suspended_sale_id = phppos_sales_suspended_payments.sale_id );
ALTER TABLE `phppos_sales` DROP  `suspended_sale_id`;
DROP TABLE phppos_sales_suspended_items_taxes;
DROP TABLE phppos_sales_suspended_item_kits_taxes;
DROP TABLE phppos_sales_suspended_payments;
DROP TABLE phppos_sales_suspended_items;
DROP TABLE phppos_sales_suspended_item_kits;
DROP TABLE phppos_sales_suspended;

Answer 1

如果不需要担心外键关系，您可以这样做:

ALTER TABLE `sales` 
    ADD COLUMN `old_id` int(11) NOT NULL default 0;
INSERT INTO `sales`
    (`field2`, `field3`, `field4`, `old_id`)
    SELECT
        `field2`,
        `field3`,
        `field4`,
        `id`
    FROM
        `layaway`;

所以，基本上，您基本上插入了除自动递增 id 字段之外的所有内容。之后您可以:

DROP TABLE `layaway`;

然后，您可以更新其他外键，如下所示:

UPDATE
    `table2`,
    `sales`
SET
    `table2`.`layaway_id` = `sales`.`id`
WHERE
    `table2`.`layaway_id` = `sales`.`old_id`

希望有帮助。