看完这篇文章，妈妈再也不担心我的MySQL表名大小写不一致了

     数据库迁移到linux的mysql上，应用访问找不到表名，查看日志发现原来是查询的表名称与数据库当中的表名称大小写不一致。这是因为MySQL在Window下不区分大小写而Linux环境下的MySQL数据库的表名默认是区分大小写的。其实不光表名称，数据库名称、变量名在Linux也是区分大小写的，而只有列名和列的别名是忽略大小写的。

解决的办法也很简单，只需要5步

1.首先将数据库当中的表备份

2.删除库中的表

3.修改Linux上的MySQL的配置文件/etc/my.cnf ， 在[mysqld]下面添加一行配置  lower_case_table_names=1

4.重启数据库

5.将备份的表结构和表数据导入

虽然上面的解决了问题，但如果我非要一致呢？

答案是那就改名呗！可是几张还好，几百张怎么办！！

别着急，办法还是有的，那就是使用MySQL存储过程来解决，直接上代码来看看效果 

-- MySQL将数据库当中的表名称转换成大写
DROP PROCEDURE IF EXISTS tableNamesToUppercase;
DELIMITER $
CREATE PROCEDURE tableNamesToUppercase(IN dbname VARCHAR(200))
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE oldname VARCHAR(200);
	DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN cur;
	REPEAT
	FETCH cur INTO oldname;
	SET @newname = UPPER(oldname);
	SET @isNotSame = @newname <> BINARY oldname;
	IF NOT done && @isNotSame THEN
	SET @SQL = CONCAT(' RENAME TABLE `', oldname, '` TO `', LOWER(@newname), '_tmp` ');
	PREPARE tmpstmt FROM @SQL;
	EXECUTE tmpstmt;
	SET @SQL = CONCAT(' RENAME TABLE `', LOWER(@newname), '_tmp` TO `', @newname, '`');
	PREPARE tmpstmt FROM @SQL;
	EXECUTE tmpstmt;
	DEALLOCATE PREPARE tmpstmt;
	END IF;
	UNTIL done END REPEAT;
	CLOSE cur;
END$
DELIMITER ;

call tableNamesToUppercase('要转换的库名称');

转换前

 

 转换后

 心情瞬间是不是舒适了~~~

当然还有转换成小写的 这个就不给大家贴图了，直接上代码

-- MySQL将数据库当中的表名称转换成小写
DROP PROCEDURE IF EXISTS tableNamesToUppercase;
DELIMITER $
CREATE PROCEDURE tableNamesToUppercase(IN dbname VARCHAR(200))
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE oldname VARCHAR(200);
	DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN cur;
	REPEAT 
	FETCH cur INTO oldname;
	SET @newname = LOWER(oldname);
	SET @isNotSame = @newname <> BINARY oldname;
	IF NOT done && @isNotSame THEN
	SET @SQL = CONCAT(' RENAME TABLE `', oldname, '` TO `', UPPER(@newname), '_tmp` ');
	PREPARE tmpstmt FROM @SQL;
	EXECUTE tmpstmt;
	SET @SQL = CONCAT(' RENAME TABLE `', UPPER(@newname), '_tmp` TO `', @newname, '`');
	PREPARE tmpstmt FROM @SQL;
	EXECUTE tmpstmt;
	DEALLOCATE PREPARE tmpstmt;
	END IF;
	UNTIL done END REPEAT;
	CLOSE cur;
END$
DELIMITER ;

call tableNamesToUppercase('要修改的库名称');