关于mysql:Mysql-数据库批量调整字段名称中的特殊字符

46次阅读

共计 721 个字符，预计需要花费 2 分钟才能阅读完成。

依据以下语句获取解决语句；

SELECT    case when DATA_TYPE= 'varchar' then CONCAT('ALTER TABLE',TABLE_NAME , 'CHANGE' ,COLUMN_NAME,'', REPLACE(COLUMN_NAME,'_','') ,'',DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,')',' ; ') elseCONCAT('ALTER TABLE ',TABLE_NAME ,' CHANGE ',COLUMN_NAME,'  ', REPLACE(COLUMN_NAME,'_','') ,'',DATA_TYPE,' ; ') end        as' 语句 ',
TABLE_SCHEMA AS '库名',
TABLE_NAME AS '表名',
COLUMN_NAME '列名',
ORDINAL_POSITION AS '列的排列程序',
COLUMN_DEFAULT AS '默认值',
IS_NULLABLE AS '是否为空',
DATA_TYPE AS '数据类型',
CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
NUMERIC_PRECISION AS '数值精度 (最大位数)',
NUMERIC_SCALE AS '小数精度',
COLUMN_TYPE AS 列类型,
COLUMN_KEY 'KEY',
EXTRA AS '额定阐明',
COLUMN_COMMENT AS '正文' FROM
information_schema.`COLUMNS` WHERE
TABLE_SCHEMA = '数据库名称'
    and COLUMN_NAME <> REPLACE(COLUMN_NAME, '_', '');

依据查问的语句，执行后，即可调整数据库所有字段特殊字符；

正文完