gxwebsoft
5c4ccec4f7
- 替换原有失效的身份证OCR服务为百度智能云OCR，支持身份证正面信息识别 - 身份证号、姓名、住址识别成功后回填至订单和用户信息，驱动支付流程状态 - 新增后端配置项管理百度OCR的API密钥，保证密钥仅服务端可见 - 调用失败时仅日志警告，不阻断业务，前端可手动填写身份证号兜底 - 新增数据库字段 id_code、real_name、address 支持身份证识别数据存储 - 更新确认收货接口参数，新增身份证号字段支持前端手动输入 - 更新相关代码逻辑，使用百度OCR接口实现最佳努力模式的身份证识别及回填 - 修改配置文件调整默认激活环境和数据库连接信息，支持开发与生产环境分离
41 lines
2.1 KiB
SQL
41 lines
2.1 KiB
SQL
-- 修复「确认收货」卡在"确认身份"：shop_order 缺少 id_code / address 两列。
|
||
-- real_name 已存在(real_name 重复报错 1060 已经证实)，无需再加。
|
||
-- 后端 OCR 识别到的身份证号无法落库（MyBatis-Plus updateById 会因列不存在而报错）。
|
||
-- 部署后端新代码【前】必须先在 yunxinwei 库执行本脚本。
|
||
-- 本脚本幂等安全：基于 INFORMATION_SCHEMA 判断列是否存在再决定是否执行 ALTER；
|
||
-- 即使重复跑也不会报 1060 Duplicate column。
|
||
|
||
-- 1) 先看一眼现状，便于核对
|
||
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'shop_order'
|
||
AND COLUMN_NAME IN ('id_code', 'real_name', 'address', 'comments', 'receipt_phone')
|
||
ORDER BY COLUMN_NAME;
|
||
|
||
-- 2) 条件加列：id_code
|
||
SET @sql_id_code = IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shop_order' AND COLUMN_NAME = 'id_code') = 0,
|
||
'ALTER TABLE `shop_order` ADD COLUMN `id_code` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ''身份证号'' AFTER `comments`',
|
||
'SELECT ''id_code 已存在,跳过'' AS info'
|
||
);
|
||
PREPARE stmt FROM @sql_id_code; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
-- 3) 条件加列：address
|
||
SET @sql_address = IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'shop_order' AND COLUMN_NAME = 'address') = 0,
|
||
'ALTER TABLE `shop_order` ADD COLUMN `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ''详细地址'' AFTER `real_name`',
|
||
'SELECT ''address 已存在,跳过'' AS info'
|
||
);
|
||
PREPARE stmt FROM @sql_address; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
||
|
||
-- 4) 验证最终结果
|
||
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'shop_order'
|
||
AND COLUMN_NAME IN ('id_code', 'real_name', 'address')
|
||
ORDER BY COLUMN_NAME;
|