MySQL #{驼峰字段} for MyBatis

# 查找在那个表并生成 字段名 SQL
SELECT TABLE_SCHEMA, TABLE_NAME, concat('SELECT ',
  group_concat(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR ', ' )
  , ' FROM ' , TABLE_SCHEMA, '.', TABLE_NAME, ';') AS SQLStr
FROM information_schema.COLUMNS WHERE 1=1
AND TABLE_NAME like 'job_depend' GROUP BY TABLE_SCHEMA, TABLE_NAME ORDER BY length(TABLE_SCHEMA);

# #{驼峰字段} (创建存储函数snakeToCamel后使用)
SELECT TABLE_SCHEMA, TABLE_NAME,
  snakeToCamel(group_concat(concat('#{',COLUMN_NAME,'}')
  ORDER BY ORDINAL_POSITION SEPARATOR ', ')) AS COLUMN_NAMES
FROM information_schema.COLUMNS WHERE 1=1
AND TABLE_NAME like 'job_depend' GROUP BY TABLE_SCHEMA, TABLE_NAME;

# 字段名=#{驼峰字段} SQL (创建存储函数snakeToCamel后使用)
SELECT TABLE_SCHEMA, TABLE_NAME, concat('UPDATE ', TABLE_NAME, ' SET\n',
  group_concat(concat('  ', COLUMN_NAME,' = #{',snakeToCamel(COLUMN_NAME),'}')
  ORDER BY ORDINAL_POSITION SEPARATOR ',\n')
  , '\nWHERE col=val;')AS SQLStr
FROM information_schema.COLUMNS WHERE 1=1
AND TABLE_NAME like 'job_depend' GROUP BY TABLE_SCHEMA, TABLE_NAME;

# 字段注释
SELECT TABLE_SCHEMA, TABLE_NAME,
  group_concat(CASE COLUMN_COMMENT
               WHEN '' THEN COLUMN_NAME
               ELSE COLUMN_COMMENT END
  ORDER BY ORDINAL_POSITION SEPARATOR ', ' ) AS COLUMN_NAMES
FROM information_schema.COLUMNS WHERE 1=1
AND TABLE_NAME like 'job_depend' GROUP BY TABLE_SCHEMA, TABLE_NAME;

# 查找在那个表并生成 字段名 AS 注释 SQL
SELECT TABLE_SCHEMA, TABLE_NAME,
  concat('SELECT ',group_concat(
    CASE COLUMN_COMMENT
    WHEN '' THEN COLUMN_NAME
    ELSE concat_ws(' AS ', COLUMN_NAME, COLUMN_COMMENT)
    END
  ORDER BY ORDINAL_POSITION SEPARATOR ', ')
  , ' FROM ' , TABLE_SCHEMA, '.', TABLE_NAME, ';') AS SQLStr
FROM information_schema.COLUMNS WHERE 1=1
AND TABLE_NAME like 'job_depend' GROUP BY TABLE_SCHEMA, TABLE_NAME;

# 打开 MySQL8 默认关闭的创建存储函数开关
SET GLOBAL log_bin_trust_function_creators = 1;
# 若存储过程存在则删除
DROP FUNCTION IF EXISTS snakeToCamel;
# 创建一个存储函数
CREATE FUNCTION snakeToCamel(snakeString VARCHAR(16383))
  # 返回值类型
  RETURNS VARCHAR(16383)
  # 开始内容
  BEGIN
    set snakeString = replace(snakeString, '_a', 'A');
    set snakeString = replace(snakeString, '_b', 'B');
    set snakeString = replace(snakeString, '_c', 'C');
    set snakeString = replace(snakeString, '_d', 'D');
    set snakeString = replace(snakeString, '_e', 'E');
    set snakeString = replace(snakeString, '_f', 'F');
    set snakeString = replace(snakeString, '_g', 'G');
    set snakeString = replace(snakeString, '_h', 'H');
    set snakeString = replace(snakeString, '_i', 'I');
    set snakeString = replace(snakeString, '_j', 'J');
    set snakeString = replace(snakeString, '_k', 'K');
    set snakeString = replace(snakeString, '_l', 'L');
    set snakeString = replace(snakeString, '_m', 'M');
    set snakeString = replace(snakeString, '_n', 'N');
    set snakeString = replace(snakeString, '_o', 'O');
    set snakeString = replace(snakeString, '_p', 'P');
    set snakeString = replace(snakeString, '_q', 'Q');
    set snakeString = replace(snakeString, '_r', 'R');
    set snakeString = replace(snakeString, '_s', 'S');
    set snakeString = replace(snakeString, '_t', 'T');
    set snakeString = replace(snakeString, '_u', 'U');
    set snakeString = replace(snakeString, '_v', 'V');
    set snakeString = replace(snakeString, '_w', 'W');
    set snakeString = replace(snakeString, '_x', 'X');
    set snakeString = replace(snakeString, '_y', 'Y');
    set snakeString = replace(snakeString, '_z', 'Z');
    set snakeString = replace(snakeString, '_', '');
    RETURN snakeString;
  END;