mysql、oracle大小写问题

一、mysql创建数据库的字符集与排序规则

 

1、字符集说明：

     

一般选择utf8.下面介绍一下utf8与utf8mb4的区别：

utf8mb4兼容utf8，且比utf8能表示更多的字符。至于什么时候用，看你的做什么项目了，http://blog.csdn.net/leelyliu/article/details/52879685看unicode编码区从1 ～ 126就属于传统utf8区，当然utf8mb4也兼容这个区，126行以下就是utf8mb4扩充区，什么时候你需要存储那些字符，你才用utf8mb4,否则只是浪费空间。

2、排序说明

排序一般分为两种：utf8_bin和utf8_general_ci

bin 是二进制, a 和 A 会别区别对待.

例如你运行:

SELECT * FROM table WHERE txt = 'a'

那么在utf8_bin中你就找不到 txt = 'A' 的那一行, 而 utf8_general_ci 则可以.

utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。

utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果

utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容。

 

排序规则：

排序规则的后半部份即后缀 含义：
　　_BIN 二进制排序
　　_CI(CS) 是否区分大小写，CI不区分，CS区分
　　_AI(AS) 是否区分重音，AI不区分，AS区分
　　_KI(KS) 是否区分假名类型,KI不区分，KS区分
　　_WI(WS) 是否区分宽度 WI不区分，WS

3、utf8_unicode_ci和utf8_general_ci区别：

相同点：

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。

区别：

utf8_general_ci校对速度快，但准确度稍差。（准确度够用，一般建库选择这个）。

utf8_unicode_ci准确度高，但校对速度稍慢。

 

二、Mysql大小写问题

1、简介

    在MySQL中，数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个，取决于存储引擎)。因此，所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。

在大多数Unix中数据库名和表名对大小写敏感，而在Windows中对大小写不敏感。一个显著的例外情况是Mac OS X，它基于Unix但使用默认文件系统类型(HFS+)，对大小写不敏感。然而，Mac OS X也支持UFS卷，该卷对大小写敏感，就像Unix一样。

字符串大小写敏感和Mysql的数据库的名字、表名字、字段名字、还有字段值有关。

       lower_case_file_system说明：

是否数据目录所在的文件系统对文件名的大小写敏感。

ON说明对文件名的大小写不敏感，

OFF表示敏感。

      lower-case-table-names说明：

取值范围有三个，分别是0、1、2.

1. 设置成0：表名按你写的SQL大小写存储，大写就大写小写就小写，比较时大小写敏感。

2. 设置成1：表名转小写后存储到硬盘，比较时大小写不敏感。

3. 设置成2：表名按你写的SQL大小写存储，大写就大写小写就小写，比较时统一转小写比较。

 

 例如在windows下查看：

 说明windows系统对大小写不敏感，MySQL也默认设置为对大小写不敏感。

 

2、大小写区分规则

1.  默认情况下

    linux：

           1、数据库名与表名是严格区分大小写的；

          2、表的别名是严格区分大小写的；

          3、列名与列的别名在所有的情况下均是忽略大小写的；

          4、变量名也是严格区分大小写的；

    windows：

          都不区分大小写（变量名默认也不区分大小写了！！！）

    Mac OS下（非UFS卷）：

          都不区分大小写（变量名默认也不区分大小写了！！！）

 

如果想在查询时区分字段值的大小写，则字段值需要设置BINARY属性，设置的方法有多种：

     a）创建时设置：

     CREATE TABLE T(A VARCHAR(10) BINARY);

     b）使用alter修改

 

参数说明（lower_case_table_names）

• 与数据库名、表名、存储过程、触发器有关：

unix下lower_case_table_names默认值为 0；Windows下默认值是 1 ; Mac OS X下默认值是 2 .

值： 0

使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母在硬盘上保存表名和数据库名。名称比较对大小写敏感。在大小写不敏感的操作系统如windows或Mac OS x上我们不能将该参数设为0，如果在大小写不敏感的文件系统上将--lowercase-table-names强制设为0，并且使用不同的大小写访问MyISAM表名，可能会导致索引破坏。

值： 1

      表名在硬盘上以小写保存，名称对大小写不敏感。MySQL将所有表名转换为小写在存储和查找表上。该行为也适合数据库名和表的别名。该值为Windows的默认值。

值： 2

      表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母进行保存，但MySQL将它们转换为小写在查找表上。名称对大小写不敏感，即按照大小写来保存，按照小写来比较。注释：只在对大小写不敏感的文件系统上适用! innodb表名用小写保存。

• 和字段名有关：

字段名是不区分大小写的。

• 和字段值有关

字段值的大小写由Mysql的collate来控制。提到collate，就不得不说字符集。字符集是一套符号和编码，collate是在字符集内用于比较字符的一套规则，比如定义'A'<'B'这样的关系的规则。不同的字符集有多种校对规则，一般而言，collate以其相关的字符集名开始，通常包括一个语言名，并且以

_ci（大小写不敏感）、   _cs（大小写敏感）或_bin（二元）结束 。

　mysql中collate是校对集的意思，可以理解为，排序规则等。一个字符集可能有多种校对集合。

比如 utf8字符集：

utf8_general_ci,表示不区分大小写，这个是utf8字符集默认的collate；

utf8_general_cs表示区分大小写，

utf8_bin表示二进制比较，同样也区分大小写。

1. 查看数据库的字符集相关信息

mysql> show variables like '%character%';

+--------------------------+----------------------------------+

| Variable_name            | Value                            |

+--------------------------+----------------------------------+

| character_set_client     | utf8                             |

| character_set_connection | utf8                             |

| character_set_database   | utf8                             |

| character_set_filesystem | binary （二进制）                          |

| character_set_results    | utf8                             |

| character_set_server     | utf8                             |

| character_set_system     | utf8                             |

| character_sets_dir       | /usr/local/mysql/share/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.00 sec)

 

2、查看数据库collation相关信息

mysql> show variables like '%collation%';

+----------------------+-----------------+

| Variable_name        | Value           |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database   | utf8_general_ci |

| collation_server     | utf8_general_ci |

+----------------------+-----------------+

3 rows in set (0.01 sec)

---------------------

 

1. 由大小写敏感转换为不敏感方法

    如果原来所建立库及表都是对大小写敏感的，想要转换为对大小写不敏感，主要需要进行如下3步：

    1.将数据库数据通过mysqldump导出。

    2.在my.cnf（CentOS下配置文件是/etc/my.cnf）中更改最后添加 lower_case_tables_name = 1，

 

#add by LiShuo 20181226 start

lower_case_table_names=1

#add by LiShuo 20181226 en

然后重启mysql数据库

service mysqld restart

    3.将导出的数据导入mysql数据库。

 

注意事项

    为了避免大小写引发的问题，一种推荐的命名规则是：在定义数据库、表、列的时候全部采用小写字母加下划线的方式，不使用任何大写字母

    在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时，看不出名字原来是用大写还是小写。

    请注意在Unix中如果以前lower_case_tables_name = 0将lower_case_tables_name设置为1之前，重启mysqld之前，必须先将旧的数据库名和表名转换为小写。

4、深入分析

字段值的大小写由mysql的校对规则来控制。提到校对规则，就不得不说字符集。字符集是一套符号和编码，校对规则是在字符集内用于比较字符的一套规则  .

一般而言，校对规则以其相关的字符集名开始，通常包括一个语言名，并且以

_ci（大小写不敏感）、

_cs（大小写敏感）

_bin（二元）结束 。

比如 utf8字符集，

utf8_general_ci,表示不区分大小写，这个是utf8字符集默认的校对规则； utf8_general_cs表示区分大小写;

utf8_bin表示二进制比较，同样也区分大小写 。

三、oracle大小写问题

1、表名、字段名

oracle表和字段是有大小写的区别。oracle默认是大写，如果我们用双引号括起来的就区分大小写，如果没有，系统会自动转成大写。

 

2、字符串大小写问题:

具体字段值是区分大小写。

键字 用户 密码 字段名 表名 序列名 触发器名等是不区分的。

3、大小写转换函数：

在oracle中，有一些字符函数：
upper(字符串)：转换为大写
lower(字符串)：转换为小写
initcap(字符串)：首字母大写
replace(字符串1，字符串2，字符串3)：将串1中所有的串2，替换成串3

1、将小写字符变成大写字符：UPPER(字符串)

SELECT UPPER(‘smith’) FROM DUAL;
在查询中使用UPPER函数
SELECT * FROM emp where ename=UPPER(‘Smith’);

2、将大写字符变成小写字符：LOWER(字符串)

SELECT LOWER(‘SMITH’) FROM DUAL;

3、将单词首字母大写，其他字符小写：INITCAP(字符串)

SELECT INITCAP(‘HELLO world!’) FROM DUAL;
在查询中使用INITCAP函数
SELECT INITCAP(ename) FROM emp;

4、内容替换：replace()

SELECT replace('Hello', 'l', 'x') FROM DUAL;