mysql - 将非关系表拆分为多个关系表

Question

我在 MySQL 5 数据库中有一个包含 60000 行的表，该表源自 CSV 文件:

CREATE TABLE `smts_import` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `AscCode` varchar(5) NOT NULL,
  `AscName` varchar(50) NOT NULL,
  `RgnCode` varchar(5) NOT NULL,
  `RgnName` varchar(100) NOT NULL,
  `SCode` varchar(30) NOT NULL,
  `SName` varchar(100) NOT NULL,
  `AM` int(11) NOT NULL,
  `AF` int(11) NOT NULL,
  `LG` decimal(10,4) NOT NULL,
  `LT` decimal(10,4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ars_codes` (`AscCode`,`RgnCode`,`SCode`),
  KEY `s_code` (`SCode`)
);

其中包含大量重复数据。 SCode 是唯一的，RgnCode 和 SCode 字段之间是多对一的关系，AscCode 和 RgnCode 字段之间也是多对一的关系。我想以关系方式将数据拆分(规范化)到三个单独的表中，这样就不会重复数据:

CREATE TABLE `ascs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a_code` varchar(6) NOT NULL,
  `a_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a_code` (`a_code`)
);

CREATE TABLE `rgns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a_id` int(11) NOT NULL,
  `r_code` varchar(6) NOT NULL,
  `r_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a_id`+`r_code` (`a_id`, `r_code`),
  KEY `r_code` (`r_code`)
);

CREATE TABLE `sms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `r_id` int(11) NOT NULL,
  `s_code` varchar(16) NOT NULL,
  `s_name` varchar(100) NOT NULL,
  `a_m` int(11) NOT NULL,
  `a_f` int(11) NOT NULL,
  `lg` decimal(10,4) NOT NULL,
  `lt` decimal(10,4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `r_id+s_code` (`r_id`,`s_code`),
  KEY `s_code` (`s_code`)
);

哪里rgns 。 a_id是 ascs 上的外键。 id ，和sms 。 r_id是 rgns 上的外键。 id .

我已经创建了三个表并成功填充了前两个表，ascs和rgns ，具有来自 smts_import 的独特数据 table 。当我尝试填充第三个表 sms 时，我的问题出现了只需 SCode , SName , AM , AF , LG和LT来自 smts_import 的字段表，加上 rgns 中的相应 ID table 。在这里我只是迷路了，我尝试了以下多种变体:

INSERT INTO sms (r_id, s_code, s_name, a_m, a_f, lg, lt)
SELECT DISTINCT sr.id, SCode, SName, AM, AF, LG, LT
FROM sms_import AS si, rgns AS sr, ascs AS sa
WHERE (sr.r_code = si.RgnCode)
AND (sr.a_id = sa.id)
AND (sa.a_code = si.AscCode)
ORDER BY SCode

但我最终得到了太多的记录。如何编写此插入语句以从 sms_import 的所有记录中获取适当的字段表，加上 sms 中的正确值。 r_id来自 rgns 的字段。 id领域？

感谢您的帮助

Answer 1

“RgnCode和SCode字段之间的关系是多对一的”因此，每个 SCode 您还会获得许多 rgns.id。如果您选择不同的 rgns.id、SCode 等，您将多次获得每个 SCode，具体取决于您有多少个 RgnCode。我建议向 sms 表添加一个 id 列，并创建一个单独的表 sms2rgns，其中包含一对多关系 sms.id -> rgns.id