带你使用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在字段上可以兼容不同语言，也挺不错的。

参考：