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

在没有或者很少使用映射类的时候，从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;

运行结果如下：

下面是直接转化为可写在代码中的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()函数
2. mysql查找字符串位置_mysql 定位字符串的位置