USE invoice_pool;
-- 定义字段添加顺序及依赖关系
SET @columns = JSON_ARRAY(
JSON_OBJECT('name', 'create_dept', 'type', 'bigint(20)', 'after', 'id', 'comment', '创建部门'),
JSON_OBJECT('name', 'jkrnsrsbh', 'type', 'varchar(50)', 'after', 'voucher_number','comment', '缴款人纳税识别号'),
JSON_OBJECT('name', 'jkr', 'type', 'varchar(50)', 'after', 'jkrnsrsbh', 'comment', '缴款人'),
JSON_OBJECT('name', 'skdw', 'type', 'varchar(100)','after', 'jkr', 'comment', '收款单位'),
JSON_OBJECT('name', 'fhr', 'type', 'varchar(50)', 'after', 'skdw', 'comment', '复核人'),
JSON_OBJECT('name', 'skr', 'type', 'varchar(50)', 'after', 'fhr', 'comment', '收款人'),
JSON_OBJECT('name', 'kpr', 'type', 'varchar(50)', 'after', 'skr', 'comment', '开票人'),
JSON_OBJECT('name', 'pjmc', 'type', 'varchar(50)', 'after', 'kpr', 'comment', '票据名称'),
JSON_OBJECT('name', 'xmqd', 'type', 'text', 'after', 'pjmc', 'comment', '项目清单'),
JSON_OBJECT('name', 'xmmx', 'type', 'text', 'after', 'xmqd', 'comment', '项目明细'),
JSON_OBJECT('name', 'qtxx', 'type', 'text', 'after', 'xmmx', 'comment', '其他信息'),
JSON_OBJECT('name', 'detail_url', 'type', 'text', 'after', 'qtxx', 'comment', '税局查验截图url')
);
-- 遍历字段配置
SET @index = 0;
WHILE @index < JSON_LENGTH(@columns) DO
-- 提取当前字段配置
SET @columnConfig = JSON_EXTRACT(@columns, CONCAT('$[', @index, ']'));
SET @colName = JSON_UNQUOTE(JSON_EXTRACT(@columnConfig, '$.name'));
SET @colType = JSON_UNQUOTE(JSON_EXTRACT(@columnConfig, '$.type'));
SET @colAfter = JSON_UNQUOTE(JSON_EXTRACT(@columnConfig, '$.after'));
SET @colComment = JSON_UNQUOTE(JSON_EXTRACT(@columnConfig, '$.comment'));
-- 检查目标字段是否存在
SET @exists = (
SELECT COUNT(*) FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = 'invoice_wallet_head'
AND column_name = @colName
);
-- 动态生成 ALTER 语句
IF @exists = 0 THEN
-- 检查依赖字段是否存在
SET @afterExists = (
SELECT COUNT(*) FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = 'invoice_wallet_head'
AND column_name = @colAfter
);
IF @afterExists > 0 THEN
SET @sql = CONCAT(
'ALTER TABLE invoice_wallet_head ADD COLUMN ',
@colName, ' ', @colType, ' NULL DEFAULT NULL COMMENT ''',
@colComment, ''' AFTER `', @colAfter, '`'
);
ELSE
SET @sql = CONCAT(
'ALTER TABLE invoice_wallet_head ADD COLUMN ',
@colName, ' ', @colType, ' NULL DEFAULT NULL COMMENT ''',
@colComment, ''''
);
END IF;
-- 执行并记录
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT CONCAT('已添加字段: ', @colName) AS Message;
ELSE
SELECT CONCAT('跳过字段: ', @colName, ' (已存在)') AS Message;
END IF;
SET @index = @index + 1;
END WHILE;执行这段脚本报了 错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE @index < JSON_LENGTH(@columns) DO
最新发布