MySQL 数据类型详解

在 MySQL 中常见的数据类型如下：

1、 整数类型

    包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，小数类型 FLOAT 和 DOUBLE，DECIMAL。

2、 日期/时间类型

    包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

3、 字符串类型

    包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

4、 二进制类型

    包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

 

整数类型

类型名称	说明	存储需求	字节
TINYINT	-128~127	0~255	1字节
SMALLINT	-32768~32767	0~65535	2字节
MEDIUMINT	-8388608~8388607	0~16777215	3字节
INT	-2147483648~2147483647	0~4294967295	4字节
BIGINT	-9223372036854775808~9223372036854775807	0~18446744073709551616	8字节

 

例：

-- 判断存在及删除

DROP TABLE IF EXISTS test_int;

-- 创建表

CREATE TABLE test_int (

    tinyint_value TINYINT,

    smallint_value SMALLINT,

    mediumint_value MEDIUMINT,

    int_value INT,

    bigint_value BIGINT

) ENGINE=INNODB CHARSET=utf8;

-- 添加数据

INSERT INTO test_int VALUES(100, 200, 300, 400, 500);

-- 查询表

SELECT * FROM test_int

 

表结构

数据

小数类型

 

类型名称	说明	字节数
FLOAT	单精度浮点数	4字节
DOUBLE	双精度浮点数	8字节
DECIMAL (M, D)	精确的小数,M是总位数，D是小数点后的位数	M+2字节

 

例：

-- 判断存在及删除

DROP TABLE IF EXISTS test_decimal;

-- 创建表

CREATE TABLE test_decimal (

    float_value FLOAT,

    double_value DOUBLE,

    decimal_value DECIMAL(10,2)

) ENGINE=INNODB CHARSET=utf8;

-- 添加数据

INSERT INTO test_decimal VALUES(1.25, 2.50, 3.75);

-- 查询表

SELECT * FROM test_decimal

 

表结构

数据

 

日期/时间类型

 

类型名称	日期格式	日期范围	字节
YEAR	YYYY	1901 ~ 2155	1字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4字节

例：

-- 判断存在及删除

DROP TABLE IF EXISTS test_time;

-- 创建表

CREATE TABLE test_time (

    date_value DATE,

    time_value TIME,

    year_value YEAR,

    datetime_value DATETIME,

    timestamp_value TIMESTAMP

) ENGINE=INNODB CHARSET=utf8;

-- 添加数据

INSERT INTO test_time VALUES(NOW(), NOW(), NOW(), NOW(), NOW());

-- 查询表

SELECT * FROM test_time

 

表结构

数据

 

字符串类型

 

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

 

主要 CHAR 与 VARCHAR类型

    CHAR类型不管存储的值的长度是多少，都会占用M个字节，而VARCHAR则占用实际长度+1个字节，如下表所示。

 

插入值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
' '	'    '	4字节	''	1字节
'ab'	'ab  '	4字节	'ab'	3字节
'abc'	'abc '	4字节	'abc'	4字节
'abcd'	'abcd'	4字节	'abcd'	5字节
'abcdef'	'abcd'	4字节	'abcd'	5字节

 

-- 判断存在及删除

DROP TABLE IF EXISTS test_str;

-- 创建表

CREATE TABLE test_str (

    date_value CHAR(2),

    time_value VARCHAR(2)

) ENGINE=INNODB CHARSET=utf8;

-- 添加数据

INSERT INTO test_str VALUES('ab', 'abc');

-- 查询表

SELECT * FROM test_str

 

及TEXT系列

    TEXT系列的存储范围比VARCHAR要大，当VARCHAR不满足时可以用TEXT系列中的类型。需要注意的是TEXT系列类型的字段不能有默认值，在检索的时候不存在大小写转换，没有CHAR和VARCHAR的效率高

二进制类型 

 

    主要讲解一个 bit 类型 Bit称为位数据类型，其数据有两种取值：0和1，长度为1位。在输入0以外的其他值时，系统均把它们当1看待。这种数据类型常作为逻辑变量使用，用来表示真、假或是、否等二值选择。

注意：

    创建BIT类型字段的时候，BIT(),括号里的数字设为1时，

    BIT类型在数据库中存储为0/1，或为FALSE/TRUE；

    查询的时候 BIT类型可以写为 0，1，TRUE，FALSE，或带单引号；

    在实体映射中BIT类型映射为BOOLEAN类型；

 

-- 判断存在及删除

DROP TABLE IF EXISTS test_bit;

-- 创建表

CREATE TABLE test_bit (

    bit_value BIT

) ENGINE=INNODB CHARSET=utf8;

-- 添加数据

INSERT INTO test_bit VALUES('1');

-- 查询表

SELECT * FROM test_bit WHERE bit_value=TRUE