MySQL資料表中內容大小寫區分的設定，mysql大小寫

MySQL資料表中內容大小寫區分的設定，mysql大小寫
MYSQL在預設的情況下查詢是不區分大小寫，例如：  ?

1234567

mysql>createtable t1( ->namevarchar(10));Query OK, 0 rowsaffected (0.09 sec)  mysql>insertinto t1 values('you'),('You'),('YOU');Query OK, 3 rowsaffected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0

 

 對這個表，預設情況下，下面兩個查詢的結果是一樣的：  ?

12345678910111213141516171819

mysql>select* fromt1 wherename = 'you';+------+|name| +------+| you | | You | | YOU | +------+3rowsin set (0.00 sec)  mysql>select* fromt1 wherename = 'YOU';+------+|name| +------+| you | | You | | YOU | +------+3rowsin set (0.00 sec)

 

 如果想讓MYSQL知道你輸入的字母是大寫還是小寫，修改表：  ?

1234567891011121314151617181920

mysql>altertable t1 change namename varchar(10)binary;Query OK, 3 rowsaffected (0.20 sec) Records: 3 Duplicates: 0 Warnings: 0   mysql>select* fromt1 wherename = 'you';+------+|name| +------+| you | +------+1 row inset (0.00 sec)  mysql>select* fromt1 wherename = 'YOU';+------+|name| +------+| YOU | +------+1 row inset (0.00 sec)

 

如果你只是想在SQL語句中實現的話：  ?

123456789101112131415

mysql>select* fromt1 wherename = binary'YOU';+------+|name| +------+| YOU | +------+1 row inset (0.02 sec)  mysql>select* fromt1 wherename = binary'you';+------+|name| +------+| you | +------+1 row inset (0.00 sec)

 

 如果不想這麼麻煩而想服務一開啟就讓大小寫一致的話： 可以修改my.ini或者my.cnf ?

123	[mysqld]lower_case_table_names=1（0：區分；1：不區分）

 

然後重啟MYSQL服務。  ?

1234567

mysql> show variables like'%case_table%';+------------------------+-------+| Variable_name | Value | +------------------------+-------+| lower_case_table_names | 1 | +------------------------+-------+1 row inset (0.00 sec)