mysql基础——基本数据类型
2020-08-23 本文已影响0人
chase_lwf
内容
1 数值类型
2 浮点数类型
3 字符串类型
4 日期类型
5 其他类型
数值类型
类型 大写 范围 范围（无符号）
TINYINT 1 byte (-128，127) (0，255)
SMALLINT 2 bytes (-32 768，32 767) (0，65 535)
MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215)
INT 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295)
BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615)
注：int(1) 和int(10)有什么区别？int(m)
1 数值类型中后面的m的大小只有在字段设置了 zerofill 属性后，才有区别，如果字段没有设置了这个属性，那么写多少都没有区别；
2 当字段设置zerofill 后，m的含义是字段显示的宽度，当实际存储的值的宽度没有m大时，显示时会在前面补0，当宽度超过m后，没有区别；实际存储的值还是一样的，int也是用4个字节存储，换句话说，m不影响实际存储，只是在显示上有区别
看下面例子：
mysql> create table t3(id int(4) zerofill);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(11111);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 0001 |
| 0002 |
| 0003 |
| 11111 |
+------+
浮点数类型
Float: 4字节存储，如果没有指定精度和标度，按照原来的精度存储，有效数字是8位，超过8位数字会四舍五入
Double: 8个字节存储，如果没有指定精度和标度，按照原来的精度存储，有效数字是16位
Decimal: 定点数据类型
定义 这些类型的数据时，同时需要指定其长度和精度 ，即用Float(M,D)来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。
如果 float 和 double 不指定精度，则按照实际的精度来显示， decimal 不指定时，默认为(10,2)。
这里，还有一个问题，对于 float 和 double，如果不指定精度，则不会存储小数点末尾的0，只有指定了具体的精度值，才会存储小数点末尾的0。
字符串类型
类型 大小 含义
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 极大文本数据
1 关于char(n)和varchar(n)：n都是指定了该列最大可存储的字符数，如 char(6) 说明该列只能存储6个字符
2 char是定长的，如char(4)，当存储的值没有达到4个字符时，如存储‘ab’, 则实际存储时会在后面添加空字符串补齐，实际存储的值是‘ab ’，char字段存储值的末尾有空格时，会被丢弃，查询时会根据trim把多余空格去掉 ; varchar(4)是定长的，当实际存储值没有达到4个字符时，不会补齐，会按实际内容存储；
3 char限制了最大的字符数是255，不管是什么编码时，都是可允许最大存储字符是255个，实际存储占用的空间根据不同的编码方式不定；
4 varchar最大存储限制是65535个字节，根据不同的编码方式，可存储的最大字符数不一样，例如utf-8编码时，一个中文字符占3个字节，那么实际可存储的最大字符数： n= (65535-1-2)/3 个，减1时当字段设置default null时，会用一个字节来标识，当长度超过255时，会用2个字节来存储字段的实际长度，小于255是用1个字节存储长度；
5 当实际存储超过n时，如果字段模式严格模式，则报错，如果是非严格模式，则会把超过的部分截取丢掉；
6 mysql一行的数据有大小限制，一行数据存储空间最大为65535，所以一般情况，当一个表中不仅仅只有varchar一列时，该varchar列的最大存储量是达不到65535的，因为其他列也占用了存储；
7 text的最大存储也是65535byte,但是text列相当于在数据页只存储了地址，真实的数据是存储在外存的，所以不会像varchar一样占用每行64k这个最大存储量太多
8 text不能默认为空字符串，如果没有值，那么是null
日期类型
日期时间类型 占用空间 日期格式 最小值 最大值 零值表示
DATETIME 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59 0000-00-00 00:00:00
TIMESTAMP 4 bytes YYYY-MM-DD HH:MM:SS 1980-01-01 00:00:01 UTC 2040-01-19 03:14:07 UTC 00000000000000
DATE 4 bytes YYYY-MM-DD 1000-01-01 9999-12-31 0000-00-00
TIME 3 bytes HH:MM:SS -838:59:59 838:59:59 00:00:00
YEAR 1 bytes YYYY 1901 2155 0000
TIMESTAMP和DATETIME比较
相同点：两者都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期。
不同点：他们的的存储方式，大小(字节)，表示的范围不同。
TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
DATETIME，不做任何改变，基本上是原样输入和输出。
总结：TIMESTAMP和DATETIME 都可用来表示YYYY-MM-DD HH:MM:SS 类型的日期， 除了存储方式和存储范围以及大小不一样，没有太大区别。但对于跨时区的业务，TIMESTAMP更为合适。
其他类型
ENUM
CREATE TABLE tickets (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
priority ENUM('Low', 'Medium', 'High') NOT NULL
);
SET
参考：[https://www.cnblogs.com/wtsgtc/p/10387007.html](https://www.cnblogs.com/wtsgtc/p/10387007.html)
多选字符串数据类型，适合存储“多个值”。
设定set的时候，同样需要设定“固定的几个值”；存储的时候，可以存储其中的若干个值。
设定set的格式：字段名称 SET("选项1","选项2",...,'选项n')
同样的，set的每个选项值也对应一个数字，依次是1，2，4，8，16...，最多有64个选项
使用的时候，可以使用set选项的字符串本身（多个选项用逗号分隔），也可以使用多个选项的数字之和（比如：1+2+4=7）
-1.创建及插入
/* 创建表格 */
create table set_table(
id int auto_increment primary key,
hobby set('music','movie','swimming','footbal')
);
/* 使用选项的字符串格式 */
insert into set_table(id,hobby) values(null,'M','music');
/* 使用选项的字符串格式 */
mysql> insert into enum_set_table(id,hobby) values(null,'F','music,movie,footbal');
/* 使用选项的数字格式 */
insert into set_table(id,hobby) values(null,1);
/* 使用选项的数字格式，15=1+2+4+8 <=> 'music,movie,swimming,footbal' */
insert into set_table(id,hobby) values(null,15);
/* 查询结果 */
select * from enum_set_table;
--2.查询
--找出SET_col包含value set成员的行。
SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
--在其它地方找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。
SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
--寻找包含第1个set成员的值。
SELECT * FROM tbl_name WHERE set_col & 1;
--寻找一个确切匹配的值。
SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
--应注意将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。
