48 lines
1.4 KiB
SQL
48 lines
1.4 KiB
SQL
-- ========================================
|
||
-- 修复订单表中的无效地址数据
|
||
-- 问题：service_address字段存储了数字（如：453535）
|
||
-- 解决：将无效地址更新为NULL，让前端使用逆地理编码
|
||
-- ========================================
|
||
|
||
-- 1. 查看当前有多少订单的地址是纯数字
|
||
SELECT
|
||
COUNT(*) as total_invalid_address,
|
||
GROUP_CONCAT(DISTINCT service_address) as invalid_addresses
|
||
FROM `order`
|
||
WHERE service_address REGEXP '^[0-9]+$';
|
||
|
||
-- 2. 查看这些订单的详细信息
|
||
SELECT
|
||
id,
|
||
order_no,
|
||
student_name,
|
||
service_address,
|
||
service_date,
|
||
status
|
||
FROM `order`
|
||
WHERE service_address REGEXP '^[0-9]+$'
|
||
ORDER BY id DESC
|
||
LIMIT 20;
|
||
|
||
-- 3. 将纯数字的地址更新为NULL
|
||
-- 注意：执行前请先备份数据！
|
||
UPDATE `order`
|
||
SET service_address = NULL
|
||
WHERE service_address REGEXP '^[0-9]+$';
|
||
|
||
-- 4. 验证修复结果
|
||
SELECT
|
||
COUNT(*) as total_null_address
|
||
FROM `order`
|
||
WHERE service_address IS NULL OR service_address = '';
|
||
|
||
-- ========================================
|
||
-- 说明：
|
||
-- 1. 地址为NULL时，前端会自动使用逆地理编码获取真实地址
|
||
-- 2. 如果需要保留原始数据，可以先创建备份表
|
||
-- 3. 建议在订单创建时就使用逆地理编码获取正确地址
|
||
-- ========================================
|
||
|
||
-- 可选：创建备份表
|
||
-- CREATE TABLE `order_backup_20260126` AS SELECT * FROM `order`;
|