数据类型

在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;