带你使用mysql将字段转为驼峰法

约定不等于承诺〃 2022-09-12 08:52 580阅读 0赞

在没有或者很少使用映射类的时候，从Mapper返回值都是`Map`或者`List<Map>`这个时候，在返回的Map中由于要使用驼峰命名，字段如果少还好，十几几十个字段的时候，可以自己编写工具来处理，我就用MYSQL实现了一个将下划线转为驼峰法的SQL。如下：

SELECT
    	COLUMN_NAME,
    	CASE
    		WHEN LOCATE('_', COLUMN_NAME) = 0 THEN COLUMN_NAME
    		ELSE
    			CONCAT(
    				SUBSTR(COLUMN_NAME, 1, LOCATE('_', COLUMN_NAME) - 1),
    				UPPER(SUBSTR(COLUMN_NAME, LOCATE('_', COLUMN_NAME) +1, 1)),
    				SUBSTR(COLUMN_NAME, LOCATE('_', COLUMN_NAME) +2)
    			)
    		END AS camelCaseColumnName,
    	SUBSTR(COLUMN_NAME, 1, LOCATE('_', COLUMN_NAME) - 1) AS prf,
    	UPPER(SUBSTR(COLUMN_NAME, LOCATE('_', COLUMN_NAME) +1, 1)) AS upp,
    	SUBSTR(COLUMN_NAME, LOCATE('_', COLUMN_NAME) +2) AS sfx
    FROM information_schema.`COLUMNS`
    WHERE TABLE_SCHEMA = 'roadnetworkmonitor' AND TABLE_NAME = 'tm_wastebook_weather'
    ORDER BY ORDINAL_POSITION;

运行结果如下：  
![MYSQL字段转为驼峰法SQL][MYSQL_SQL]  
下面是直接转化为可写在代码中的SQL

SELECT 
     CONCAT('tww.', tmp.COLUMN_NAME, ' AS ', tmp.camelCaseColumnName, ',') AS line
    FROM
    (
    	SELECT
    		COLUMN_NAME,
    		CASE
    			WHEN LOCATE('_', COLUMN_NAME) = 0 THEN COLUMN_NAME
    			ELSE
    				CONCAT(
    					SUBSTR(COLUMN_NAME, 1, LOCATE('_', COLUMN_NAME) - 1),
    					UPPER(SUBSTR(COLUMN_NAME, LOCATE('_', COLUMN_NAME) +1, 1)),
    					SUBSTR(COLUMN_NAME, LOCATE('_', COLUMN_NAME) +2)
    				)
    			END AS camelCaseColumnName,
    		SUBSTR(COLUMN_NAME, 1, LOCATE('_', COLUMN_NAME) - 1) AS prf,
    		UPPER(SUBSTR(COLUMN_NAME, LOCATE('_', COLUMN_NAME) +1, 1)) AS upp,
    		SUBSTR(COLUMN_NAME, LOCATE('_', COLUMN_NAME) +2) AS sfx
    	FROM information_schema.`COLUMNS`
    	WHERE TABLE_SCHEMA = 'blog' AND TABLE_NAME = 'article'
    	ORDER BY ORDINAL_POSITION
    ) tmp

另外说点：  
我在学习python的时候，要求使用的是下划线，因此如果能统一全部使用下划线的话，那么java和python在字段上可以兼容不同语言，也挺不错的。

参考：

1.  [MySQL中的substr()函数][MySQL_substr]
2.  [mysql查找字符串位置\_mysql 定位字符串的位置][mysql_mysql]

[MYSQL_SQL]: /images/20220828/96a4bbb9c7f94fec9c5fba24bcf34c30.png
[MySQL_substr]: https://www.cnblogs.com/ahu-lichang/p/9494909.html
[mysql_mysql]: https://blog.csdn.net/weixin_28864167/article/details/113127323