MySQL大小写问题

来源：互联网发布：快手特效软件大全编辑：程序博客网时间：2024/05/23 19:13

数据库名、表名、表别名大小写

window下均不区分大小写(Why？)
Linux默认情况下区分大小写

 mysql> show variables like 'lower%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | OFF   || lower_case_table_names | 0     |+------------------------+-------+2 rows in set (0.01 sec)--修改发现变量为只读变量mysql> set global lower_case_table_names=1;ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable修改my.cnf后重启数据库lower_case_table_names=1mysql> show variables like 'lower%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| lower_case_file_system | OFF   || lower_case_table_names | 1     |+------------------------+-------+2 rows in set (0.01 sec)mysql> use test;Database changedmysql> create database Test;ERROR 1007 (HY000): Can't create database 'test'; database existsmysql> create table T  as select * from t;ERROR 1050 (42S01): Table 'T' already exists

变量含义解释

lower_case_file_system：数据库所在的文件系统对文件名大小写敏感度。ON表示大小写不敏感 OFF表示敏感

lower_case_table_names：表名大小写敏感度

0表示使用Create语句指定的大小写保存文件
1表示大小写敏感文件系统以小写保存
2表示使用Create语句指定的大小写保存文件，但MySQL会将之转化为小写(?)

(当Linux设置为2时，错误日志显示[Warning] lower_case_table_names was set to 2, even though your the file system '/home/mysql/master_a/data/' is case sensitive. Now setting lower_case_table_names to 0 to avoid future problems.)

列名大小写

列名列别名均不区分大小写

字段值大小写

1、表与行的collation，bin与cs区分大小写，ci不区分大小写

MySQL对collation约定的命名方式如下：

*_ci:case insensitive collation，不区分大小写
*_cs: case sensitive collation，区分大小写
*_bin: 表示的是binary case sensitive collation，区分大小写的

# 指定collate为区分大小写mysql> create table b( id varchar(10)) default charset=utf8 default collate=utf8_bin; Query OK, 0 rows affected (0.03 sec)sec)mysql> insert into b values ('A'),('a'),('B');Query OK, 3 rows affected (0.02 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from b where id like 'a';+------+| id   |+------+| a    |+------+1 row in set (0.00 sec)mysql> select id,count(*) from b group by id;+------+----------+| id   | count(*) |+------+----------+| A    |        1 || B    |        1 || a    |        1 |+------+----------+3 rows in set (0.03 sec)

# 指定collate为忽略大小写mysql> create table t (name varchar(10)) default charset=utf8 default collate=utf8_general_ci;Query OK, 0 rows affected (0.09 sec)mysql> insert into t values('a'),('A'),('B'),('b'),('c');Query OK, 5 rows affected (0.02 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> select * from t where name like 'a';+------+| name |+------+| a    || A    |+------+2 rows in set (0.01 sec)mysql> select name,count(*) from t group by name;+------+----------+| name | count(*) |+------+----------+| a    |        2 || B    |        2 || c    |        1 |+------+----------+3 rows in set (0.04 sec)

2、字段指定binary

mysql> alter table t change name name varchar(10) binary;Query OK, 5 rows affected (0.08 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> select * from t where name like 'A';+------+| name |+------+| A    |+------+1 row in set (0.00 sec)mysql> select name,count(*) from t group by name;+------+----------+| name | count(*) |+------+----------+| A    |        1 || B    |        1 || a    |        1 || b    |        1 || c    |        1 |+------+----------+5 rows in set (0.01 sec)

整理自网络

Svoid
2014-07-03

0 0