前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL #{驼峰字段} for MyBatis

MySQL #{驼峰字段} for MyBatis

作者头像
林万程
发布2018-08-02 15:46:13
4.8K0
发布2018-08-02 15:46:13
举报
文章被收录于专栏:IT开发技术与工作效率
代码语言:javascript
复制
# 查找在那个表并生成 字段名 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;
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2018.07.31 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档