MySQL数据类型、字符集、引擎和索引

数据类型

在MySQL中将数据类型分为以下几种：

• 数值类型（整型、浮点型）
• 字符串类型
• 日期时间类型
• 复合类型
• 空间类型（非科学性工作基本不用，不做了解）

数值类型

1.整型

MySQL数据类型	所占字节	值范围（有符号）
tinyint	1字节	-128~127
smallint	2字节	-32768~32767
mediumint	3字节	-8388608~8388607
int	4字节	-2147483648~2147483647
bigint	8字节	+-9.22*10的18次方

注意：

• 默认存在符号位，使用unsigned 属性表示无符号。
• 显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，使用zerofill 属性修改。
  例：int(6) 插入一个数’1234’，补填后为’001234’
• 业务中需要存储多大的值就选用对应的类型。
• 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。

2.浮点型

MySQL数据类型	所占字节	值范围
float(m,d)	4字节	单精度浮点型，m总个数，d小数位
double(m,d)	8字节	双精度浮点型，m总个数，d小数位
decimal(m,d)		定点数decimal是存储为字符串的浮点数

注意:

• 浮点数范围由所指定的m和d决定。
• m既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。

字符串类型

MySQL数据类型	所占字节	值范围（有符号）
CHAR	0-255字节	定长字符串
VARCHAR	0-255字节	变长字符串
TINYBLOB	0-255字节	不超过255个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777215字节	中等长度文本数据
LONGBLOB	0-4294967295字节	二进制形式的极大文本数据
LONGTEXT	0-4294967295字节	极大文本数据
VARBINARY(M)	允许长度0-M个字节的定长字符串	值的长度+1个字节
BINARY(M)	M	允许长度0-M个字节的定长字符串

注意：

• CHAR类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。大小范围0-255.比指定长度长的值将被截断，短的会用空格填补。
• VARCHAR类型可以根据实际内容动态改变存储值的长度，所以在不确定字段需要多少字符时使用VARCHAR可以大大节约磁盘空间、提高存储效率。

日期时间类型

MySQL数据类型	所占字节	值范围
date	3字节	日期，格式：2010-09-09
time	3字节	时间，格式：08:30:30
datetime	8字节	日期时间，格式：2010-09-09 08:30:30
timestamp	4字节	时间戳，自动存储记录修改的时间
year	1字节	年份,格式: 2010

注意：

• 一般用整型保存时间戳，因为PHP可以很方便的将时间戳进行格式化。
• 也有人为了方便在数据库中查看，使用日期时间datetime类型存储时间。

复合类型

MySQL数据类型	说明	示例
set	集合类型	set(val1,val2,…val64)
enum	枚举类型	enum(val1,val2,…val65535)

ENUM类型

• ENUM类型只允许在集合中取得一个值，有点类似于单选项。比如人类的性别。
• ENUM类型可以从集合取得一个值或者使用null值，除此之外的输入将会使MySQL在这个字段中插入一个空字符串。
• ENUM类型最多可以包含65535个元素，其中一个元素被MySQL保留用来存储错误信息，这个错误信息用索引0或空字符串表示。
• MySQL认为ENUM类型集合中的元素为合法输入，其他任何输入都将失败。这说明可以通过搜索空字符串或对应索引为0的行来找到错误的记录。

SET类型

• SET类型可以从集合中取得任意数量的值。
• 输入非集合中的值，也会在字段插入空字符串。
• 最多包含64个元素。

字符集

为了更好的识别中文、日文、英文等，对于常用的符号进行了编码，这个编码就是字符集。字符集确定了文字的存储方式。

字符集	说明	字节长度
ASCII	美国标准信息交换代码	单字节
GBK	汉字内码扩展规范	双字节
unicode	万国码	4字节
UTF-8	Unicode的可变长度字符编码	1-6个字节

注意：

• 实际工作中常用的字符集utf-8和GBK。

表引擎

MySQL的强大之处在于它的插件式存储引擎，我们可以基于表的特点使用不同的存储引擎，从而达到最好的性能。

引擎名称	说明
MyISAM	常用，读取效率很高
InnoDB	常用，支持写入和事务处理等
Archive	不常用，归档引擎，压缩比高达1：10，用于数据归档
NDB	不常用，集群服务器中使用

MyISAM

不支持事务，表锁（表级锁，加锁会锁住整个表），支持全文索引，操作速度快。常用于读取多的业务。

• MyISAM由myd和myi组成。.myd用来存放数据文件，.myi用来存放索引文件。
• 对于MyISAM存储引擎表，mysql数据库只缓存其索引文件，数据文件的缓存由操作系统本身完成。

InnoDB

1. 支持事务，主要面向在线事务处理（OLTP）方面的应用。
2. 行锁设计，支持外键，默认情况读取操作不加锁。

注：

• 行锁：写入、更新操作时将这一行加锁，不让其他人操作。
• 表锁：写入、更新时将整个表加锁，不让其他人操作。
• 事务：同时操作多个数据，其中一个数据操作失败可回滚到操作之前，常用于银行、电商、金融系统中。

索引

类似于书的目录，帮助快速定位到有用的信息。索引越多写入、修改的速度越慢。因为写入修改数据时，也要修改对应的索引。

MySQL的索引类型

索引类型	说明	语法	语法说明
普通索引	基本的索引	alter table 表 add index(某字段)	为表的某字段增加索引
唯一索引	某一行使用唯一索引，这一列的每行数据都要求是唯一的	alter table 表 add unique(某字段)	为表的某字段增加唯一索引
主键索引	特殊的唯一索引，不允许有空值。	alter table 表 add primary key(字段)	增加主键索引
全文索引	对于需要全局搜索的数据	alter table 表 add fulltext(字段)	增加全文索引

创建表时声明索引

PRIMARY KEY(字段)
INDEX [索引名] (字段)
FULLTEXT [索引名] (字段)
UNIQUE [索引名] (字段)
索引名可选。

示例如下：

CREATE TABLE user_sg (
    id INT NOT NULL,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(20) NOT NULL,
    content VARCHAR(30) NOT NULL,
    PRIMARY KEY(id),
    INDEX pw (password),
    UNIQUE(username),
    FULLTEXT(content)
) ENGINE=InnoDB;