表名大小写配置
mysql表名默认区分大小写
# 表名小写查询
mysql> select * from big_data where name = 'test999999';
+---------+------------+------+--------------------+
| id | name | age | email |
+---------+------------+------+--------------------+
| 1000000 | test999999 | 9 | test999999@163.com |
+---------+------------+------+--------------------+
1 row in set (0.00 sec)
# 表名大写查询
mysql> select * from BIG_data where name = 'test999999';
ERROR 1146 (42S02): Table 'test.BIG_data' doesn't exist
- 查看配置
mysql> show global variables like '%lower_case%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
- 配置说明
lower_case_file_system
:表示当前系统文件是否大小写敏感,只读参数,无法修改
- ON:大小写不敏感
- OFF:大小写敏感
lower_case_table_names
:标识存储在磁盘是大小写和是否区分大小写
- 1:小写存储,不区分大小写
- 0:按实际存储,区分大小写
- 2:按实际存储,按小写比较
修改表名不区分大小写
修改表名不区分大小写
- linux
vim /etc/mysql/my.cnf
# 添加
lower_case_table_names=1
# 重启mysql即可
- 新建docker mysql
docker run -p 3306:33066 --name mysql -v
/home/docker/mysql/conf:/home/docker/mysql/conf.d -v
/home/docker/mysql/logs:/home/docker/mysql/logs -v
/home/docker/mysql/data:/home/docker/mysql/data -e
MYSQL_ROOT_PASSWORD=123456 -d mysql
--lower_case_table_names=1
- 修改docker mysql
# 直接修改
docker exec mysql bash -c 'echo "lower_case_table_names=1" >>
/etc/mysql/my.cnf'
# 复制出来修改再复制回去
docker cp mysql:/etc/mysql/mysql.conf.d/mysqld.cnf .
docker cp mysqld.cnf mysql:/etc/mysql/my.cnf
# 重启
docker restart mysql
字段大小写配置
mysql字段默认不区分大小写
mysql> select * from big_data where name = 'test999999';
+---------+------------+------+--------------------+
| id | name | age | email |
+---------+------------+------+--------------------+
| 1000000 | test999999 | 9 | test999999@163.com |
+---------+------------+------+--------------------+
1 row in set (0.01 sec)
# 字段大写
mysql> select * from big_data where name = 'Test999999';
+---------+------------+------+--------------------+
| id | name | age | email |
+---------+------------+------+--------------------+
| 1000000 | test999999 | 9 | test999999@163.com |
+---------+------------+------+--------------------+
1 row in set (0.00 sec)
修改字段区分大小写
- 查询时自断签加上
binary
select * from big_data where binary name = 'Test999999';
- 修改表字段约束
alter table big_data modify column email varchar(64) binary default null;