解决linux下mysql区分大小写问题

linux 下，mysql 的默认是区分大小写的，而windows 下默认不区分大小写，我们大多数在windows 下开发，之后迁移到linux。我们可以通过修改配置使得在linux下，myql 表名不区分大小写。（以下分几种情况介绍）

一、如果有/etc/my.cnf文件，则直接修改此文件并重启mysql

1.在[mysqld]下面添加一句：

 lower_case_table_names =1

2.重启mysql

service mysql restart

3.进入mysql，输入命令查看是否配置成功

show variables like "%case%";   （显示为1即为成功）

二、如果没有/etc/my.cnf文件，但是有/usr/share/mysql/my-default.cnf文件

1.将/usr/share/mysql/my-default.cnf文件复制一份到/etc/下，并改名为my.cnf

cp /usr/share/mysql/my-default.cnf /etc/my.cnf

2.在[mysqld]下面添加一句： 

lower_case_table_names =1

3.重启mysql

service mysql restart

4.进入mysql，输入命令查看是否配置成功

show variables like "%case%";   （显示为1即为成功）

三、如果linux下这两个文件都无法找到（从5.7.18开始不在二进制包中提供my-default.cnf文件）

1.创建新文件/etc/my.cnf，并编辑以下内容（内容为从其他版本mysql中提取的样例）

vi /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

 

[mysqld]

 

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

 

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

 

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

 

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

2.在[mysqld]下面添加一句： 

lower_case_table_names =1

3.重启mysql

service mysql restart

4.进入mysql，输入命令查看是否配置成功

show variables like "%case%";   （显示为1即为成功）