94 lines
2.4 KiB
SQL
94 lines
2.4 KiB
SQL
/*
|
||
Navicat Premium / MySQL 5.7+
|
||
将 blade_region 的中国国家编码统一为 +86。
|
||
|
||
适用场景：
|
||
1. 旧数据使用 code = '00' 作为中国根节点；
|
||
2. 旧数据使用 code = '86' 或 parent_code / ancestors = '86'；
|
||
3. 当前数据没有国家根节点，但省级数据 parent_code 已经是 '86'。
|
||
*/
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
START TRANSACTION;
|
||
|
||
-- 1. 确保新的中国国家节点存在。
|
||
INSERT INTO `blade_region` (
|
||
`code`,
|
||
`parent_code`,
|
||
`ancestors`,
|
||
`name`,
|
||
`province_code`,
|
||
`province_name`,
|
||
`city_code`,
|
||
`city_name`,
|
||
`district_code`,
|
||
`district_name`,
|
||
`town_code`,
|
||
`town_name`,
|
||
`village_code`,
|
||
`village_name`,
|
||
`region_level`,
|
||
`sort`,
|
||
`remark`
|
||
)
|
||
SELECT
|
||
'+86',
|
||
'0',
|
||
'0',
|
||
COALESCE(
|
||
(SELECT r.`name` FROM (SELECT `code`, `name` FROM `blade_region`) r WHERE r.`code` IN ('86', '00') ORDER BY FIELD(r.`code`, '86', '00') LIMIT 1),
|
||
'中华人民共和国'
|
||
),
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
'',
|
||
0,
|
||
1,
|
||
''
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM (SELECT `code` FROM `blade_region` WHERE `code` = '+86') exists_region
|
||
);
|
||
|
||
-- 2. 如果 +86 已存在，仍强制修正它的根节点关系。
|
||
UPDATE `blade_region`
|
||
SET
|
||
`parent_code` = '0',
|
||
`ancestors` = '0',
|
||
`region_level` = 0
|
||
WHERE `code` = '+86';
|
||
|
||
-- 3. 省级节点统一挂到 +86 下。
|
||
UPDATE `blade_region`
|
||
SET `parent_code` = '+86'
|
||
WHERE `parent_code` IN ('00', '86');
|
||
|
||
-- 4. ancestors 按逗号分隔 token 精确替换，避免误伤 110086 这类编码。
|
||
UPDATE `blade_region`
|
||
SET `ancestors` = TRIM(BOTH ',' FROM REPLACE(REPLACE(CONCAT(',', `ancestors`, ','), ',00,', ',+86,'), ',86,', ',+86,'))
|
||
WHERE FIND_IN_SET('00', `ancestors`) > 0
|
||
OR FIND_IN_SET('86', `ancestors`) > 0;
|
||
|
||
-- 5. 清理旧中国根节点，避免树上出现多个国家根。
|
||
DELETE FROM `blade_region`
|
||
WHERE `code` IN ('00', '86')
|
||
AND (`region_level` = 0 OR `name` IN ('中华人民共和国', '中国'));
|
||
|
||
COMMIT;
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- 验证：
|
||
-- SELECT `code`, `parent_code`, `ancestors`, `name`, `region_level` FROM `blade_region` WHERE `code` = '+86';
|
||
-- SELECT `code`, `parent_code`, `ancestors`, `name`, `region_level` FROM `blade_region` WHERE `parent_code` = '+86' ORDER BY `code` LIMIT 20;
|
||
-- SELECT `code`, `parent_code`, `ancestors`, `name` FROM `blade_region` WHERE `parent_code` IN ('00', '86') OR FIND_IN_SET('00', `ancestors`) > 0 OR FIND_IN_SET('86', `ancestors`) > 0;
|