MySQL基础之字符类型学习笔记

客官°小女子只卖身不卖艺 2021-08-13 18:40 490阅读 0赞

### 文章目录 ###

*   *  一、字符集和字符编码
     *   *  1.1、字符集
         *  1.2、字符编码
     *  二、字符集排序规则
     *   *  2.1、排序规则定义
         *  2.2 、排序规则特征
     *  三、CHAR和VARCHAR
     *   *  3.1、CHAR类型
         *  3.2、VARCHAR类型
     *  四、BINARY和VARBINARY
     *   *  4.1、特性对比
         *  4.2、例子实践
     *  五、BLOB和TEXT
     *   *  5.1、text类型
         *  5.2、blob类型
         *  5.3、排序问题
         *  5.4、索引问题
     *  六、ENUM和SET类型
     *   *  6.1、集合类型简介
         *  6.2、集合类型例子

## 一、字符集和字符编码 ##

### 1.1、字符集 ###

*  字符集：字符集就是字符的集合，例如GB2312是中国国家标准的简体中文字符集，GB2312收录简化汉字（6763个）及一般符号、序号、数字、拉丁字母、日文假名、希腊字母、俄文字母、汉语拼音符号、汉语注音字母，共 7445 个图形字符。

字符集查询sql：

# 方法一：直接show charset
    SHOW CHARSET;
     #方法二：查询information_schema实例的character_sets
    SELECT * FROM information_schema.character_sets;

我本地是5.7.22版本，支持的字符集就有如下：

<table> 
 <thead> 
  <tr> 
   <th>Charset</th> 
   <th>Description</th> 
   <th>Default collation</th> 
   <th>Maxlen</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>big5</td> 
   <td>Big5 Traditional Chinese</td> 
   <td>big5_chinese_ci</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>dec8</td> 
   <td>DEC West European</td> 
   <td>dec8_swedish_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>cp850</td> 
   <td>DOS West European</td> 
   <td>cp850_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>hp8</td> 
   <td>HP West European</td> 
   <td>hp8_english_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>koi8r</td> 
   <td>KOI8-R Relcom Russian</td> 
   <td>koi8r_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>latin1</td> 
   <td>cp1252 West European</td> 
   <td>latin1_swedish_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>latin2</td> 
   <td>ISO 8859-2 Central European</td> 
   <td>latin2_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>swe7</td> 
   <td>7bit Swedish</td> 
   <td>swe7_swedish_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>ascii</td> 
   <td>US ASCII</td> 
   <td>ascii_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>ujis</td> 
   <td>EUC-JP Japanese</td> 
   <td>ujis_japanese_ci</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>sjis</td> 
   <td>Shift-JIS Japanese</td> 
   <td>sjis_japanese_ci</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>hebrew</td> 
   <td>ISO 8859-8 Hebrew</td> 
   <td>hebrew_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>tis620</td> 
   <td>TIS620 Thai</td> 
   <td>tis620_thai_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>euckr</td> 
   <td>EUC-KR Korean</td> 
   <td>euckr_korean_ci</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>koi8u</td> 
   <td>KOI8-U Ukrainian</td> 
   <td>koi8u_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>gb2312</td> 
   <td>GB2312 Simplified Chinese</td> 
   <td>gb2312_chinese_ci</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>greek</td> 
   <td>ISO 8859-7 Greek</td> 
   <td>greek_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>cp1250</td> 
   <td>Windows Central European</td> 
   <td>cp1250_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>gbk</td> 
   <td>GBK Simplified Chinese</td> 
   <td>gbk_chinese_ci</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>latin5</td> 
   <td>ISO 8859-9 Turkish</td> 
   <td>latin5_turkish_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>armscii8</td> 
   <td>ARMSCII-8 Armenian</td> 
   <td>armscii8_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>utf8</td> 
   <td>UTF-8 Unicode</td> 
   <td>utf8_general_ci</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>ucs2</td> 
   <td>UCS-2 Unicode</td> 
   <td>ucs2_general_ci</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>cp866</td> 
   <td>DOS Russian</td> 
   <td>cp866_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>keybcs2</td> 
   <td>DOS Kamenicky Czech-Slovak</td> 
   <td>keybcs2_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>macce</td> 
   <td>Mac Central European</td> 
   <td>macce_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>macroman</td> 
   <td>Mac West European</td> 
   <td>macroman_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>cp852</td> 
   <td>DOS Central European</td> 
   <td>cp852_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>latin7</td> 
   <td>ISO 8859-13 Baltic</td> 
   <td>latin7_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>utf8mb4</td> 
   <td>UTF-8 Unicode</td> 
   <td>utf8mb4_general_ci</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>cp1251</td> 
   <td>Windows Cyrillic</td> 
   <td>cp1251_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>utf16</td> 
   <td>UTF-16 Unicode</td> 
   <td>utf16_general_ci</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>utf16le</td> 
   <td>UTF-16LE Unicode</td> 
   <td>utf16le_general_ci</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>cp1256</td> 
   <td>Windows Arabic</td> 
   <td>cp1256_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>cp1257</td> 
   <td>Windows Baltic</td> 
   <td>cp1257_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>utf32</td> 
   <td>UTF-32 Unicode</td> 
   <td>utf32_general_ci</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>binary</td> 
   <td>Binary pseudo charset</td> 
   <td>binary</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>geostd8</td> 
   <td>GEOSTD8 Georgian</td> 
   <td>geostd8_general_ci</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>cp932</td> 
   <td>SJIS for Windows Japanese</td> 
   <td>cp932_japanese_ci</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>eucjpms</td> 
   <td>UJIS for Windows Japanese</td> 
   <td>eucjpms_japanese_ci</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>gb18030</td> 
   <td>China National Standard GB18030</td> 
   <td>gb18030_chinese_ci</td> 
   <td>4</td> 
  </tr> 
 </tbody> 
</table>

### 1.2、字符编码 ###

*  字符编码：字符编码是将字符映射为特定的字节或者字节序列，不过一般是特定的字符集采用特定的编码方式

字符编码查询sql：

#方法一：直接show collation
    SHOW COLLATION;
    #方法二：查询information_schema的collations
    SELECT * FROM information_schema.collations;

查询一下字符编码，如下是5.7.22版本的，有222条记录：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]

> 注意：MySQL的字符集设置不进可以设置整一张表，也可以细到具体的每个字段上，用法是在建表或者修改字段时候加上`charset [字符集名称]`

## 二、字符集排序规则 ##

### 2.1、排序规则定义 ###

排序规则(Collation)：排序规则是指对字符集下字符的比较规则。

### 2.2 、排序规则特征 ###

排序规则特征：

*  每个字符集都有一个默认的排序规则
 *  一个字符集对应一个排序规则，两个不同字符集不能有相同的排序规则

> 注意：排序规则常用的命名规范有：以\_ci结尾的表示大小写不敏感(case insensitive)，以\_cs结尾的表示大小写敏感(case sensitive)，以\_bin结尾的表示二进制的比较(binary)

如图，选择一下collation，5.7.22版本的，大部分都是以ci结尾的，也就是大小写不敏感  
![在这里插入图片描述][20191024115101294.png]  
例子：

# 建表，字符编码默认是utf8_general_ci
     CREATE TABLE t (
    a VARCHAR(10)
     )CHARSET = utf8;
     # 写入两条数据
     INSERT INTO t SELECT 'a';
     INSERT INTO t SELECT 'A';
     # 查出两条数据，说明大小写不敏感
    SELECT * FROM t WHERE a='a';
    # 唯一索引也是不可以建的
    ALTER TABLE t ADD UNIQUE KEY (a);

# 修改一下字符编码
    ALTER TABLE t MODIFY COLUMN a VARCHAR(10) COLLATE utf8_bin;
    # 查出一条数据，说明大小写敏感
    SELECT * FROM t WHERE a = 'a';
    # 这种情况 唯一索引是可以建的
    ALTER TABLE t ADD UNIQUE KEY (a);

## 三、CHAR和VARCHAR ##

char和varchar是两种最常见的字符串类型，其语法分别是char(N)和varchar(N)，注意其中N在MySQL4.1版本后都表示字符的长度，而非字节长度，在MySQL4.1之前版本才表示字节的长度

### 3.1、CHAR类型 ###

对于CHAR(N)，N的范围为0~255

CHAR(N)是来保存固定长度的字符串，也是根据设置的值N，假如N设置为10，不管你传什么范围内的字符串，都是固定长度为10的，因为数据库为存储列的右边进行填充(Right padded)，而在读取时候数据库又会自动将填充的字符删除.除非设置SQL\_MODE属性PAD\_CHAR\_TO\_FULL\_LENGTH，才可以正常显示

例子实践：

# 建表实践
    CREATE TABLE t (a CHAR(10));
    # 写入数据
    INSERT INTO t SELECT 'abcd';
    # 查询
    SELECT a, HEX(a), LENGTH(a), CHAR_LENGTH(a) FROM t;

![在这里插入图片描述][20191024165232136.png]

# 查询全局SQL_MODE
    SELECT @@global.sql_mode;
    # 查询会话SQL_MODE
    SELECT @@session.sql_mode; 
    # 设置SQL_MODE属性
    SET SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH';
    # 再次查询
    SELECT a, HEX(a), LENGTH(a), CHAR_LENGTH(a) FROM t;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]

### 3.2、VARCHAR类型 ###

对于varchar类型是存储可变长字符串的，意思就是varchar(10)的列，我写字符串“abcd“，该列存储的4个字符，而char(10)的列才会存储10个字符

## 四、BINARY和VARBINARY ##

### 4.1、特性对比 ###

BINARY(N)和VARBINARY(N)与前面介绍的CHAR(N)和VARCHAR(N)类型有点类似，BINARY可类比CHAR，VARBINARY可以类比VARCHAR类型。

> 对比：
> 
>  *  不同的是BINARY和VARBINARY存储的都是二进制类型的字符串，CHAR和VARCHAR存储的才是字符类型的字符串
>  *  BINARY和VARBINARY没有字符集的概念，CHAR和VARCHAR这些才有字符集的概念
>  *  BINARY(N)和VARBINARY(N)中的N表示的字节的长度，从前面我们知道CHAR(N)和VARCHAR(N)中的N自从MySQL4.1之后，就表示字符的长度
>  *  CHAR和VARCHAR进行字符比较时，是比较本身存储的字符，忽略填充的字符的，而BINARY和VARBINARY是不忽略的，比较的是二进制值的

### 4.2、例子实践 ###

SELECT 
      HEX('a'),
      HEX('a '),
      'a' = 'a ',
      BINARY('a') = BINARY('a ') ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]

## 五、BLOB和TEXT ##

可以将blob类型的列视为足够大的varbinary类型的列，也可以将text类型的列视为足够大的varchar类型的列

### 5.1、text类型 ###

TEXT 是用来存储字符大数据类型的，TEXT类型和blob类型一样，可以细分为：

*  tinytext(2^8)
 *  text(2^16)
 *  mediumtext(2^24)
 *  longtext(2^32)

### 5.2、blob类型 ###

BLOB(Binary Large Object)是用来存储二进制大数据类型的。根据存储长度的不同，blob类型可以细分为：

*  tinyblob(2^8)
 *  blob(2^16)
 *  mediumblob(2^24)
 *  longblob(2^32)

### 5.3、排序问题 ###

blob和text排序问题：  
注意： BLOB和TEXT排序时只使用列的前max\_sort\_length个字节

SET GLOBAL max_sort_length =1024;
    SELECT @@global.max_sort_length;

### 5.4、索引问题 ###

blob和text加索引问题：  
blob和text类型的列加索引时候，需要注意一些细节

*  1、对于列不能有默认值
 *  2、加索引时需要指定索引前缀长度

CREATE INDEX indexName ON tablename(columnTest(1000));

## 六、ENUM和SET类型 ##

### 6.1、集合类型简介 ###

enum和set类型都是集合类型，不同是enum最多枚举65536个元素，而set类型最多枚举64个元素

### 6.2、集合类型例子 ###

# 创建表验证问题
    CREATE TABLE t (username VARCHAR(20),sex ENUM('male','female'))ENGINE=INNODB;
    # 正常写数据
    INSERT INTO t SELECT 'David','male';
    INSERT INTO t SELECT 'john','female';
    # 写数据，因为没有对应枚举类型，不过还是可以写入，不是警告而已
    INSERT INTO t SELECT 'mariah','security';
    # 查询出来，发现sex字段没值
    select * from t ;
    # 设置严格模式的
    SET SQL_MODE = 'strict_trans_tables';
    # 再次写数据，发现不可以写
    INSERT INTO t SELECT 'mariah','security';
    # 再次查询没有写成功
    SELECT * FROM t;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/84d605d882074c21b50848ad0b84a5b0.png
[20191024115101294.png]: /images/20210813/0d5efc2930fb4046b559b7cda09d9553.png
[20191024165232136.png]: /images/20210813/a17d96f773834b9ead6b47bded08545e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210813/f343cf42bd4247a1b1d8e5dc481e9ba5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: /images/20210813/1ad3239f78fb4d6b8a87f808613d626b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]: /images/20210813/796262fc14f74f0d954c6fed7226550f.png