mysql中字段类型及字符集

た入场券 2022-07-14 02:11 238阅读 0赞

*  介绍数据库中常用字段类型
    
     *   *   *  标识符的字符
             *  SQL语句分类 
 *  字符集
    
     *  字符集概述
     *  Mysql支持的字符集简介
     *  Unicode简述
     *  怎样选择合适的字符集
     *  Mysql字符集的设置
        
         *   *  mysql 字符集和校验规则
     *  排列规则
        
         *   *  总结

# 介绍数据库中常用字段类型 #

<table> 
 <thead> 
  <tr> 
   <th>类别</th> 
   <th>数据类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>数值类型</td> 
   <td>• <code>bit(M)</code> 位类型。M指定位数，默认值1，范围1-64<br>• <code>bool/ boolean</code> 使用0或1表示真或假<br>• <code>tinyint</code> [UNSIGNED] 带符号的范围是-128到127。无符号0到255。默认是有符号【占1个字节】<br>• <code>smallint [UNSIGNED]</code> 带符号是 负的 2的15次方 到 2的15次方-1 ,无符号 2的16方 -1【占2个字节】<br>• <code>int</code> [UNSIGNED] 带符号是 负的 2的31次方 到 2的31次方-1 ,无符号 2的32方 -1 【占4个字节】<br>• <code>bigint [UNSIGNED]</code> 带符号是 负的 2的63次方 到 2的63次方-1 ,无符号 2的64方 -1【占8个字节】• <code>float [(M,D)] [UNSIGNED]</code> M指定显示长度，d指定小数位数 浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位）【占4个字节】<br>• <code>double [(M,D)] [UNSIGNED]</code> 双精度实型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位）【占8个字节】<br>• <code>decimal(M,D)</code> :数字型，128bit，不存在精度损失，常用于银行帐目计算。（28个有效位）【如果M&gt;D，为M+2否则为D+2个字节】</td> 
  </tr> 
  <tr> 
   <td>文本、二进制类型</td> 
   <td>• <code>char (size)</code> char(20) 固定长度字符串 最大255VARCHAR(size)<br>• <code>varchar (20)</code> 可变长度字符串 最大65532 【在mysql5.0中最大21844<br>• blob / longblob 二进制数据<br>• <code>text/ longtext</code> 大文本,不支持全文索引,不支持默认值,建议使用varchar</td> 
  </tr> 
  <tr> 
   <td>时间日期</td> 
   <td>• <code>date</code> 日期类型(YYYY-MM-DD),时间范围：[‘1000-01-01’到’9999-12-31’],占4个字节 <br>• <code>datetime</code>(YYYY-MM-DD HH: MM:SS),时间范围:[ ‘1000-01-01 00:00:00’到’9999-12-31 23:59:59’],占8个字节<br>• TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间,时间范围:[ ‘1970-01-01 00:00:00’到’2037-12-31 23:59:59’],占4个字节</td> 
  </tr> 
  <tr> 
   <td>String类型</td> 
   <td>• <code>enum</code> 类型：ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。在某些情况下，ENUM值也可以为空字符串(”)或NULL： <br>• <code>set</code> 类型：SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。SET最多可以有64个不同的成员 可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值</td> 
  </tr> 
 </tbody> 
</table>

#### 标识符的字符： ####

使用任意字符，数字，符号，甚至是中文。但是一些特殊的组合，例如纯数字组合，特殊符号，包括mysql是内部关键字 应该使用标识符 限定符来包裹。

> 限定符： 反引号 “ 。

#### SQL语句分类 ： ####

1.  SQL(Strucured Query Language)=DDL，DML(DQL+DML)，DCL
2.  DDL(Data Definition Language) : 数据定义语句 (create alter drop)
3.  DML(Data Management Language) : 数据操作语句 (insert update delete)
4.  DQL(Data Query Language) : 数据查询语句(select)
5.  DCL(Data Control Language) : 数据控制语句(grant revoke commit rollback)

# 字符集 #

> 字符集（character sets）是从二进制编码到规定符号集的映射。可以把它看作是如何用位来代表特定的字符；

## 字符集概述 ##

字符集是一套符号和编码的规则，不论是在oracle数据库还是在mysql数据库，都存在字符集的选择问题，而且如果在数据库创建阶段没有正确选择字符集，那么可能在后期需要更换字符集，而字符集的更换是代价比较高的操作，也存在一定的风险，所以，我们推荐在应用开始阶段，就按照需求正确的选择合适的字符集，避免后期不必要的调整。

## Mysql支持的字符集简介 ##

mysql服务器可以支持多种字符集（可以用show character set命令查看所有mysql支持的字符集），在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集，相比oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，mysql明显存在更大的灵活性。

mysql的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。字符集是用来定义mysql存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集和校对规则是一对多的关系, MySQL支持30多种字符集的70多种校对规则。

每个字符集至少对应一个校对规则。可以用SHOW COLLATION LIKE ‘utf8%’;命令查看相关字符集的校对规则。

## Unicode简述 ##

Unicode是一种编码规范。我们在这里简述一下Unicode编码产生的历史。

先从ASCII码说起，ASCII码也是一种编码规范，只不过ASCII码只能最多表示256个字符，是针对英文产生的，而面对中文、阿拉伯文之类的复杂文字，256个字符显然是不够用的。于是各个国家或组织都相继制定了符合自己语言文字的标准，比如gb2312、big5等等。但是这种各自制定自己的标准的做法显然是有很多弊端的，于是Unicode编码规范应运而生。  
Unicode也是一种字符编码方法，不过它是由国际组织设计，可以容纳全世界所有语言文字的编码方案。Unicode的学名是”Universal Multiple-Octet Coded Character Set”，简称为UCS。UCS可以看作是”Unicode Character Set”的缩写。

Unicode有两套标准UCS-2和UCS-4，前者用2个字节表示一个字符，后者用4个字节表示一个字符。以目前常用的UCS-2为例，它可以表示的字符数为2^16=65535，基本上可以容纳所有的欧美字符和绝大多数亚洲字符。

## 怎样选择合适的字符集 ##

我们建议在能够完全满足应用的前提下，尽量使用小的字符集。因为更小的字符集意味着能够节省空间、减少网络传输字节数，同时由于存储空间的较小间接的提高了系统的性能。

有很多字符集可以保存汉字，比如utf8、gb2312、gbk、latin1等等，但是常用的是gb2312和gbk。因为gb2312字库比gbk字库小，有些偏僻字（例如：洺）不能保存，因此在选择字符集的时候一定要权衡这些偏僻字在应用出现的几率以及造成的影响，不能做出肯定答复的话最好选用gbk。

## Mysql字符集的设置 ##

mysql的字符集和校对规则有4个级别的默认设置：`服务器级`、`数据库级`、`表级`和`字段级`。分别在不同的地方设置，作用也不相同。

服务器字符集和校对，在mysql服务启动的时候确定。可以在my.cnf中设置：

[mysqld]
        default-character-set=utf8

或者在启动选项中指定：  
`mysqld --default-character-set=utf8`  
或者在编译的时候指定：  
`./configure --with-charset=utf8`  
如果没有特别的指定服务器字符集，默认使用latin1作为服务器字符集。上面三种设置的方式都只指定了字符集，没有指定校对规则，这样是使用该字符集默认的校对规则，如果要使用该字符集的非默认校对规则，则需要在指定字符集的同时指定校对规则。

可以用`show variables like 'character_set_server'`;命令查询当前服务器的字符集和校对规则。

通过mysql的配置：

Character_set_client 客户端发送数据编码
        haracter_set_connection 连接层编码
        Character_set_results客户端接收数据的编码

通过 指令 `show variables like '%chara%'`;  
设置变量：Set 变量名=值  
例：`Set character_set_client = gbk`;  
统一的操作可以用 **`set names gbk`**可以完成。（简单项目通用的做法）

#### mysql 字符集和校验规则 ####

> show collation;  
> –查看某个字符集支持的校验规则,结尾： ci大小写不敏感，cs大小写敏感， bin二进制
> 
> Show character set; 可以获得MySQL支持的所有字符集。

*\* 查询的乱码出现原因:\**

1.  character\_set\_clinet 设置和客户端实际发出的不一致.
2.  charset\_set\_results 设置和客户端实际需要的编码不一致.

> 可以my.ini中：character-set-server=utf8 修改服务器的编码设置

## 排列规则 ##

当前，utf8_unicode_ci校对规则仅部分支持Unicode校对规则算法。一些字符还是不能支持。并且，不能完全支持组合的记号。这主要影响越南和俄罗斯的一些少数民族语言，如：Udmurt 、Tatar、Bashkir和Mari。
    
         utf8_unicode_ci的最主要的特色是支持扩展，即当把一个字母看作与其它字母组合相等时。例如，在德语和一些其它语言中‘ß'等于‘ss'。
    
         utf8_general_ci是一个遗留的 校对规则，不支持扩展。它仅能够在字符之间进行逐个比较。这意味着utf8_general_ci校对规则进行的比较速度很快，但是与使用utf8_unicode_ci的 校对规则相比，比较正确性较差）。
    
         例如，使用utf8_general_ci和utf8_unicode_ci两种 校对规则下面的比较相等：
         Ä = A
         Ö = O
         Ü = U
    
         两种校对规则之间的区别是，对于utf8_general_ci下面的等式成立：
         ß = s
    
         但是，对于utf8_unicode_ci下面等式成立：
         ß = ss
    
         对于一种语言仅当使用utf8_unicode_ci排序做的不好时，才执行与具体语言相关的utf8字符集 校对规则。例如，对于德语和法语，utf8_unicode_ci工作的很好，因此不再需要为这两种语言创建特殊的utf8校对规则。
    
         utf8_general_ci也适用与德语和法语，除了‘ß'等于‘s'，而不是‘ss'之外。如果你的应用能够接受这些，那么应该使用utf8_general_ci，因为它速度快。否则，使用utf8_unicode_ci，因为它比较准确。

如果你想使用gb2312编码,那么建议你使用latin1作为数据表的默认字符集,这样就能直接用中文在命令行工具中插入数据,并且可以直接显示出来.而不要使用gb2312或者gbk等字符集,如果担心查询排序等问题,可以使用binary属性约束,例如:

create table my_table ( name varchar(20) binary not null default '')type=myisam default charset latin1;

#### 总结 ####

ci是 case insensitive, 即 “大小写不敏感”, a 和 A 会在字符判断中会被当做一样的;

utf8\_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，a 和 A 会别区别对待.而且可以存二进制的内容  
utf8\_unicode\_ci和utf8\_general\_ci对中、英文来说没有实质的差别。  
utf8\_general\_ci校对速度快，但准确度稍差。  
utf8\_unicode\_ci准确度高，但校对速度稍慢。

如果你的应用有德语、法语或者俄语，请一定使用utf8\_unicode\_ci。一般用utf8\_general\_ci就够了，到现在也没发现问题。。。

用一句话概况：utf8\_unicode\_ci比较准确，utf8\_general\_ci速度比较快。通常情况下 utf8\_general\_ci的准确性就够我们用的了，在我看过很多程序源码后，发现它们大多数也用的是utf8\_general\_ci，所以新建数据 库时一般选用utf8\_general\_ci就可以了