MySQL数据类型及使用场景

时间:2023-12-14 10:42:44

MySQL数据类型介绍

整数类型

类型名称 说明 存储需求 取值范围有符号 取值范围符号
TINYINT 很小的整数 1个字节 -128~127 0-255
SMALLINT 小的整数 2个字节 32768~32767 0-65535
MEDIUMINT 中等大小的整数 3个字节 -8388608~8388607 0-16777215
INT(INTERGER) 普通大小的整数 4个字节 -2147483648 0-4294967295
BIGINT 大整数 8个字节 -9223372036854775808~9223372036854775807 0-18446744073709551615
CREATE TABLE tb_empl
(
id INT(11)
)
上面的11指的是这个字段中数据数字显示的宽度,与取值范围是无关的。
数值的位数小于指定宽度时会由空格填充,如果大于显示宽度,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能够显示出来。显示宽度只用于显示,不能限制取值范围和占用空间

浮点类型

类型名称 说明 存储需求 取值范围有符号 取值范围符号
FLOAT 单精度浮点 4个字节 -3.402823466E+38~-1.175494351E-38 0和1.175494351E-38~3.402823466E+38
DOUBLE 双精度浮点 8个字节 -1.7976931348623157E-308~1.7976931348623157E+308 0和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL(M,D) 压缩的'严格'定点数,就是指定了小数点后几位 M+2个字节
注意不论是定点还是浮点类型,如果指定的精度超出范围,则会四舍五入进行处理。

日期与时间类型

类型名称 日期格式 日期范围 存储需求
YEAR YYYY 1901-2155 1个字节
TIME HH:MM:SS -838: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 1970-01-01 00:00:01 UTC~2038-01-19 4字节

文本字符串类型

类型名称 说明
CHAR 固定长度非二进制字符串
VARCHAR 变长非二进制字符串
TINYTEXT 非常小的非二进制字符串
TEXT 小的非二进制字符串
MEDIUMTEXT 中等大小的非二进制字符串
LONGTEXT 大的非二进制字符串
ENUM 枚举类型,只能有一个枚举字符串
SET 一个设置,字符串对象可以有零个或多个SET成员

CHAR为固定长度字符串

VARCHAR是长度可变的字符串

如何选择数据类型

整数和浮点数

需要小数部分,则使用浮点数类型,列入值的范围为1-99999,若使用整数,则MEDIUMINT UNSIGNED是最好的类型,若需要存储小数,则使用FLOAT类型。
浮点类型包括FLOAT和DOUBLE类型,DOUBLE类型精度比FLOAT类型高,因此,如果要求存储精度较高时,应该选择DOUBLE类型。

浮点数和定点数

浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是:在一定长度情况下,浮点数能表示更大的数据范围,但是由于浮点数容易产生误差,因此精度高时,建议使用DECIMAL来存储。

日期与时间类型

如果只记录时间,TIME类型
如果记录日期和时间 TIMESTAMP和DATETIME
TIMESTAMP类型当插入一条记录但并没有指定TIMESTAMP这个列值时,MySQL会把TIMESTAMP列值设为当前的时间。
CREATE TABLE test1
(
names VARCHAR(20),
times TIMESTAMP NOT NULL DEFAULT NOW()
)

CHAR和VARCHAR之间的特点与选择

CHAR是固定长度字符,VARCHAR是可变长度字符
CHAR会自动删除插入数据的尾部空格,VARCHAR不会删除尾部空格
CHAR处理速度快,但是缺点就是浪费存储空间
  • 对于MyISAM 使用CHAR可以整个表静态化,从而使数据检索更快,用空间换时间。
  • 对于InnoDB使用VARCHAR,因为它的存储格式不分固定长度和可变长度,VARCHAR比较节省空间

ENUM和SET

  • ENUM只取单值,但要注意,他的索引是从1开始,加了引号就是值,不加就是索引。

  • 设定enum的格式:

    • enum("选项1","选项2","选项3",...);
    • 实际上,enum的选项都会对应一个数字,依次是1,2,3,4,5...,最多有65535个选项
    • 加了引号
    CREATE TABLE `enum2tinyint` (
    `switchs` enum('none','success','fail','delete','skip','1') DEFAULT NULL,
    `switch` tinyint(1) NOT NULL,
    KEY `switchs` (`switchs`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES ('1', '1');
    1 1
    • 不加引号
    INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES (1, '1');
    none 1
    INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES (2, '1');
    success 1
  • 设定set的格式:

    • set("选项1","选项2","选项3",...)

    • 同样的,set的每个选项值也对应一个数字,依次是1,2,4,8,16...,最多有64个选项

    create table enum_set_table(
    id int auto_increment primary key,
    hobby set('music','movie','swimming','footbal')
    ) #15=1+2+4+8 <=> 'music,movie,swimming,footbal' */
    insert into enum_set_table(id,hobby) values(null,15);
    # 等同于
    insert into enum_set_table(id,hobby) values(null,'music,movie,swimming,footbal');

BLOB和TEXT

  • BLOB是二进制字符串

    • 主要用于存储图片,音频信息
  • TEXT是非二进制字符串

    • 只能存储纯文本文件