MYSQL大小写的约定

绝地灬酷狼 2022-01-15 02:41 181阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

MYSQL在默认的情况下查询是不区分大小写的，例如：  
  
mysql> create table t1(  
    -> name varchar(10));  
Query OK, 0 rows affected (0.09 sec)  
  
mysql> insert into t1 values('you'),('You'),('YOU');  
Query OK, 3 rows affected (0.05 sec)  
Records: 3  Duplicates: 0  Warnings: 0  
`对这个表，缺省情况下，下面两个查询的结果是一样的：`

`如果想让MYSQL知道你输入的字母是大写还是小写的，修改表：`

<table style="font-family:verdana;border-collapse:collapse;"> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <code><span style="color:rgb(0,0,0);"><span style="color:rgb(0,0,255);">mysql&gt; alter table t1 change name name varchar(10) binary;<br><span style="color:rgb(0,1,2);">Query OK, 3 rows affected (0.20 sec)</span><br><span style="color:rgb(0,1,2);">Records: 3&nbsp; Duplicates: 0&nbsp; Warnings: 0</span><br></span></span></code> 
    </div> 
    <div> 
     <br> 
     <code><span style="color:rgb(0,0,0);"><span style="color:rgb(0,0,255);"></span></span></code> 
    </div> 
    <div> 
     <code><span style="color:rgb(0,0,0);"><span style="color:rgb(0,0,255);">mysql&gt; select * from t1 where name = 'you';<br><span style="color:rgb(0,1,2);">+------+</span><br><span style="color:rgb(0,1,2);">| name |</span><br><span style="color:rgb(0,1,2);">+------+</span><br><span style="color:rgb(0,1,2);">| you&nbsp; |</span><br><span style="color:rgb(0,1,2);">+------+</span><br><span style="color:rgb(0,1,2);">1 row in set (0.00 sec)</span><br><br>mysql&gt; select * from t1 where name = 'YOU';<br><span style="color:rgb(0,1,2);">+------+</span><br><span style="color:rgb(0,1,2);">| name |</span><br><span style="color:rgb(0,1,2);">+------+</span><br><span style="color:rgb(0,1,2);">| YOU&nbsp; |</span><br><span style="color:rgb(0,1,2);">+------+</span><br><span style="color:rgb(0,1,2);">1 row in set (0.00 sec)</span><br></span><span style="color:rgb(0,0,204);"></span></span></code> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

如果你只是想在SQL语句中实现的话：  
  
mysql> select \* from t1 where name = binary 'YOU';  
\+------+  
| name |  
\+------+  
| YOU  |  
\+------+  
1 row in set (0.02 sec)  
  
mysql> select \* from t1 where name = binary 'you';  
\+------+  
| name |  
\+------+  
| you  |  
\+------+  
1 row in set (0.00 sec)

如果不想这么麻烦而想服务一开启就让大小写一致的话：  
可以修改my.ini或者my.cnf  
\[mysqld\]  
 lower\_case\_table\_names=1  
（0：区分；1：不区分）  
然后重启MYSQL服务。  
  
mysql> show variables like '%case\_table%';  
\+------------------------+-------+  
| Variable\_name          | Value |  
\+------------------------+-------+  
| lower\_case\_table\_names | 1     |  
\+------------------------+-------+  
1 row in set (0.00 sec)  
注：WINDOWS系统不用修改，系统默认就是1  
LINUX 系统默认是0。因为LINUX下的脚本都是区分大小写的。

本文出自 “[上帝，咱们不见不散！][Link 1]” 博客，转载请与作者联系！

转载于:https://my.oschina.net/u/585111/blog/219488

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/b1dedcd589fb430eae6965b044f3387b.png
[Link 1]: http://yueliangdao0608.blog.51cto.com