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是非二进制字符串

    • 只能存储纯文本文件

相关文章