mysql的数据类型DECIMAL mysql 的数据类型

转载

编程小达人 2024-06-20 11:13:56

文章标签 mysql的数据类型DECIMAL mysql 数据类型数据字符串 文章分类 MySQL 数据库

MySQL数据类型

MySQL 有三大类数据类型, 分别为数字、日期\时间、字符串, 这三大类中又更细致的划分了许多子类型:

数字类型：

整数: tinyint、smallint、mediumint、int、bigint
浮点数: float、double、real、decimal

日期和时间：

date、time、datetime、timestamp、year

字符串类型：

字符串: char、varchar
文本: tinytext、text、mediumtext、longtext
二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

整型

数值型数据类型主要用来存储数字，不同的数据类型提供不同的取值范围，可以存储的值的范围越大，其所需要的存储空间也会越大。整数类型的字段可以添加AUTO_INCREMENT自增约束条件。

类型	存储	最小值	最大值
	（Bytes）	（Signed/Unsigned）	（Signed/Unsigned）
Tinyint	1	-128	127
		0	255
Smallint	2	-32 768	32 767
		0	65535
Mediumint	3	-8 388 608	8 388 607
		0	16 777 215
Int	4	-2 147 483 648	2 147 483 647
		0	4 294 967 295
Bigint	8	-9,223,372,036,854,775,808	9 223 372 036 854 775 807
		0	18 446 744 073 709 551 615

验证整型字段有无符号及范围

整型字段默认有正负号
create table t1(id tinyint);
insert into t1 values(128),(-129);  数据超出范围无法插入

利用unsigned设置TINYINT为无符号型
create table t2(x tinyint unsigned);
insert into t2 values(-1),(256);  数据类型为无符号型且超出范围无法插入

利用不同的类型可以定义不同的数据范围
create table t3(x int unsigned);
insert into t3 values(4294967296);  超出int的范围无法插入

利用zerofill 显示时，不够8位用0填充，如果超出8位则正常显示
create table t5(x int(8) unsigned zerofill);
insert into t5 values(4294967296123);

强调：对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制，所以在创建表时，如果字段采用的是整型类型，完全无需指定显示宽度，默认的显示宽度，足够显示完整当初存放的数据

严格模式

我们有时候设置了char,tinyint,存储数据时超过它们的最大存储长度，发现数据也能正常存储进去，只是mysql帮我们自动截取了最大长度。但在实际情况下，我们应该尽量减少数据库的操作，缓解数据库的压力，让它仅仅只管理数据即可，这样的情况下就需要设置安全模式

like模糊查询
    %:匹配任意数量的任意字符
    _:匹配单个数量的任意字符
    
查看数据库配置中变量名包含mode的配置参数   
show variables like "%mode%";
修改安全模式
	set session  只在当前操作界面有效
	set global   全局有效
	
修改完之后退出当前客户端重新登陆即可
set global sql_mode ='STRICT_TRANS_TABLES'

如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";

浮点型

类型	大小	范围（有符号）	范围（无符号）	用途
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对应DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

单精度:FLOAT(M,D) [UNSIGNED] [ZEROFILL],M表示数字的总长度,D表示小数点后的数字长度,UNSIGNED最大存储范围是99999.99,FLOAT的存储范围是-3.4E38~3.4E38;

双精度:DOUBLE(M,D) [ UNSIGNED ] [ZEROFILL],最大存储围为-1.79E308~1.79E308;

定点类型：浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 decimal(M,D) 参数M < 65 是总个数，D < 30 且 D < M 是小数位。

# 存储限制
float(255,30)  m最大值为255，d最大值为30
double(255,30)  m最大值为255，d最大值为30
decimal(255,30)  m最大值为65，d最大值为30

# 精确度验证
create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));

insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);


mysql> select * from t9;  随着小数的增多，精度变得不准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+

mysql> select * from t10;  随着小数的增多，精度比float要高，但也会变得不准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |  
+----------------------------------+

mysql> select * from t11;  随着小数的增多，精度始终准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 | 
+----------------------------------+

三者区别:
精度由低到高：float→double→decimal

字符类型(char与varchar)

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

char和varchar的区别：

char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
char类型的字符串检索速度要比varchar类型的快。

create table t1(name char(4))；  超出四个字符报错，不够四个字符空格补全
create table t2(name varchar(4))；    超出四个字符报错，不够四个有几个就存几个

验证存储限制
insert into t1 values('hello');  报错Data too long for column 'name' at row 1
insert into t2 values('hello');  报错Data too long for column 'name' at row 1
验证存储长度
insert into t1 values('a'); #'a    '
insert into t2 values('a'); #'a'

通过*无法查看真正的结果
select * from t1;
select * from t2;  

通过char_length()查看真正的结果
select char_length(name) from t1; # 4
select char_length(name) from t2; # 1

针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格，如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式"strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"。

日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。

类型	大小 (字节)	范围	格式	用途
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	混合日期和时间值，时间戳

create table student(
	id int,
  name char(16),
  born_year year,
  birth date,
  study_time time,
  reg_time datetime
);
insert into student values(1,'linwow','2019','2019-05-09','11:11:00','2019-11-11 11:11:11');

mysql> select * from student;
+------+--------+-----------+------------+------------+---------------------+
| id   | name   | born_year | birth      | study_time | reg_time            |
+------+--------+-----------+------------+------------+---------------------+
|    1 | linwow |      2019 | 2019-05-09 | 11:11:00   | 2019-11-11 11:11:11 |
+------+--------+-----------+------------+------------+---------------------+

枚举与集合类型

分类

枚举enum 多选一
枚举 enum 的存储原理：实际上字段上所存储的值并不是真正的字符串，而是字符串对应的下标，当系统设定枚举类型的时候会给枚举中每个元素定义一个下标，这个下标规则从1开始，
enum(1=>‘male’,2=>‘female’,3=>‘others’)
在MySQL中系统是自动进行类型转换的，如果数据碰到“+、-、*、/” 系统回自动将数据转换为数字，而普通的字符串转换成数值0，我们利用这个特点用select 字段名+0 from 表名，可以对上边的原理进行结论进行验证。
既然实际enum 字段存储的结果是数值，那么在进行数据插入的时候就可以使用对应的数值进行操作。
集合set 多选多
集合：是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制，1表示该选型被选中，0表示该选项没有被选中。
系统为set提供了多个字节进行保存，但是系统会自动计算来选择具体的存储单元。

create table user(
	id int,
  	name char(16),
  	gender enum('male','female','others')
);
insert into user values(1,'wowlin','xxx');  报错
insert into user values(2,'linwow','male');   正确！
insert into user values(3,'wow',3);   正确！

create table teacher(
	id int,
      name char(16),
  	gender enum('male','female','others'),
  	hobby set('read','sleep','sanna','sing')
);
insert into teacher values(1,'peter','male','read,sleep')  
集合也可以只存一个

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。