数据类型是什么
数据类型(data_type)是指系统中所允许的数据的类型。MySQL数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。数据库中的每个列都应该有适当的数据类型,用于限制或允许该列中存储的数据。例如,列中存储的为数字,则相应的数据类型应该为数值类型。
MYSQL常见数据类型
MySQL支持多种类型,大致可以分为四类:数值型、浮点型、日期/时间和字符串(字符)类型。
1. 整型
整数类型 | 字节 | 最小值 | 最大值 |
TINYINT | 1 | 有符号:-128 无符号:0 | 有符号:127 无符号:255 |
SMALLINT | 2 | 有符号:-32768 无符号:0 | 有符号:32767 无符号:65535 |
MEDIUMINT | 3 | 有符号:-8388608 有符号:0 | 无符号:8388607 无符号:1677215 |
INT、INTEGER | 4 | 有符号:-2147483648 无符号:0 | 有符号:2147483647 无符号:4294967295 |
BIGINT | 8 | 有符号:-9223372036854775808 无符号:0 | 有符号:9223372036854775807 无符号:18447644073709551615 |
特点:
- 如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字
- 如果插入的数值超出了整型的范围,会报out of range异常,并且插入失败
create table a(
b int(10) unsigned NOT NULL default 0
);
2. 浮点型
浮点数类型 | 字节 | 最小值 | 最大值 |
FLOAT | 4 | ±1.175494351E-38 | ±3.402823466E+38 |
DOUBLE | 8 | ±2.2250739595072014E-308 | ±1.7976931348623157E+308 |
定点整数型 | 字节 | 描述 | |
DEC(M,D) DECIMAL(M,D) | M+2 | 最大值与DOUBLE相同,给定DECIMAL 的有限制范围由M和D决定 | |
位类型 | 字节 | 最小值 | 最大值 |
BIT(M) | 1~8 | BIT(1) | BIT(64) |
定点数的精度更大 ,M是整数部位+小数部位,D是小数点后的位数。如果小数位超过指定长度,则进行四舍五入截取。整数位长度超出则同int一样报错,插入失败。m,d可省略,如果decimal,则M默认为10,D默认为0。float与double,则根据插入的数据的精度的精度来决定精度。
使用方式:
- float(M,D)
- double(M,D)
- decimal(M,D)
create table t3 (
a float(10, 2),
b double(10, 2),
c decimal(10, 2)
);
3. 字符串类型
字符串类型 | 大小 | 用途 |
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
char与varchar的区别:
4. 日期值
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
1. datetime
create table t6(
create_time datetime
);
insert into t6 values('1999-01-01 02:03:59');
insert into t6 values('1999/01/01 02:03:59');
insert into t6 values(now()); # 插入现在的时间
- time
Time值得范围是从’-838:59:59’ 到’838:59:59’,Time类型不仅可以用于表示一天的时间(必须小于24小时),还可能为某个过去的时间或两个事件的时间间隔(可以大于24小时,或者未负)
create table t7(
create_time time
);
-- 时间的范围是: [-838:59:59 - 838:59:59]
insert into t7 values('100:59:59');
insert into t7 values('-100:59:59');
- timestamp时间戳类型
- 时间戳类型在显示方面和datetime是一样的,在存储上不一样。当插入日期时,会先转换为本地时区后再存放;当查询日期时,会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是 不一样的。
- 范围从1970-1-1 0:0:0 到 2038-1-19 11:14:07
- 时间戳使用四个字节表示
- 该值大小与存储的位长有关:2**(4 * 8 -1)
create table t8(
create_time timestamp
);
insert into t8 values(now());
insert into t8 values('2000-01-01 01:01:01');
insert into t8 values('2038-01-19 11:14:07');
insert into t8 values('2038-01-19 11:14:08'); # 报错
- year
create table t9(
create_time year
);
-- 从1900年开始 - 1900+255
insert into t9 values(now());
insert into t9 values('2155');
insert into t9 values('2156'); # 报错
5. 布尔值
mysql中的bool是0和1,代表false和true。mysql默认会转化为tinyint(1)类型
MySQL数据库的布尔类型BOOL或称布尔类型BOOLEAN,等同于微整型TINYINT(1)。MySQL数据库数据类型分类中确实存在布尔类型,但是MySQL数据库并没有真正实现布尔类型,而是借助微整型的方式实现,并且创建数据库表结构的时候,即使字段定义属性设置为布尔类型BOOL或布尔类型BOOLEAN,都会被默认改写成TINYINT(1)。
create table t10(
num boolean
);
insert into t10 set num=True;
insert into t10 set num=False;
insert into t10 set num=1;
insert into t10 set num=10;
insert into t10 set num=0;
建议:MySQL数据库产品没有真正实现对布尔类型的支持,建议大家不要使用MySQL布尔类型BOOL或布尔类型BOOLEAN,而是使用数据库类型微整型TINYINT替代。
6. 枚举(enum)
enum(可能出现的元素列表),实现将所有可能出现的结果都设计好,实际上存储的数据必须是规定好的数据中的一个。
create table t4 (
name varchar(32),
sex enum('男','女','保密') default '保密'
);
-- 枚举类型的计数默认从1开始
insert into t4 set name='张三',sex=1;
-- 使用有效数据插入
insert into t4 set name='张三',sex='男';
-- 错误使用
insert into t4 set name='张三',sex='man'; -- 错误,没有该元素
优点:
- 限制了可选值
- 节省了空间
- 运行效率高
缺点:
- MySQL数据库枚举类型字段的元素增加,必须以尾部追加的方式,否则影响数据库提供数据服务;
- 枚举类型字段不再需要的元素,也不能进行删除,否则影响数据库提供数据服务;
- MySQL数据库枚举类型的字段定义属性元素值,不能随意调整其顺序,否则影响数据库提供数据服务;
枚举原理:
枚举在进行数据规范的时候(定义的时候),系统会自动建立一个数字与枚举元素的对应关系(关系放到日志中);然后在进行数据插入的时候,系统自动将字符转换成对应的数字存储,然后在进行数据提取的时候,系统就自动将数字转换成字符串显示。因为我枚举实际存储的是数值,所以可以直接插入数值
7. 集合
SET是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。set的容纳范围为64个不同的成员…set其实和枚举差不多,set指定了一个集合范围,在我们插入数据的时候,需要插入在set范围之内的元素,如果插入了未被包含的元素,那么就会发出警告…
SET最多可以有64个不同的成员。类似于复选框, 有多少可以选多少。
create table t5 (
name varchar(32),
hobby set('吃','睡','玩','喝')
);
insert into t5 values('张三','睡,玩,吃,喝');
insert into t5 values('李四','睡,玩');