如何将一个表位置拆分为两个表状态，即mysql中具有外键state_id的城市

创建所需的表，如

create table states (id int not null auto_increment primary key,
                     state_name varchar(100) not null);
create table cities (id int not null auto_increment primary key,
                     state_id int not null,
                       `city_code` int(11) NOT NULL,
                       `city_name` varchar(100) NOT NULL,
                      foreign key (state_id) references states(id));

插入相应的数据

insert into states (state_name)
select state_name from locations;
insert into cities (state_id, `city_code`, `city_name`)
select s.state_id, l.`city_code`, l.`city_name`
from locations l
join states s on s.state_name = l.state_name;

假设两个结果表的结构很简单

CREATE TABLE IF NOT EXISTS `cities` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `state_id` MEDIUMINT(8) UNSIGNED NOT NULL,
  `city_code` VARCHAR(10) DEFAULT NULL,
  `city_name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `states` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `state_name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

填充states表，确保获得唯一的值。

INSERT INTO states (state_name) SELECT DISTINCT(state_name) FROM locations;

然后填充连接初始locations表和我们之前创建的states表的cities表。

INSERT INTO cities (state_id, `city_code`, `city_name`)
    SELECT states.id, locations.city_code, locations.city_name 
    FROM `locations`
    JOIN states 
    ON states.state_name = locations.state_name;

试试

--
-- Table structure for table `cities`
--
CREATE TABLE IF NOT EXISTS `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_id` int(11) NOT NULL,
  `city_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `city_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `state_id` (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `states`
--
CREATE TABLE IF NOT EXISTS `states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `cities`
  ADD CONSTRAINT `fk_state_id` FOREIGN KEY (`state_id`) REFERENCES `states` (`id`);

我认为以下内容应该可以实现您的目标。

create table `states` (
    select `id`,`state_name` from `locations`
);
alter table `states` engine=innodb;
alter table `states` change column `id` `id` int(11) unsigned not null auto_increment first, add primary key (`id`);

create table `cities`(
    select `id` as `state_id`,`city_name`,`city_code` from `locations`
);
alter table `cities` engine=innodb;
alter table `cities` add column `id` int unsigned not null auto_increment first, add primary key (`id`);
alter table `cities` change column `state_id` `state_id` int(11) unsigned not null default '0' after `id`;
alter table `cities` add constraint `fk_state` foreign key (`state_id`) references `states` (`id`) on update cascade on delete cascade;