『SQL』数据类型
本篇博客介绍数据库中的常用数据类型。
数据类型分类
分类 | 数据类型 | 说明 |
数值类型 | BIT(M) | 位类型。M指定位数,默认值1,范围1~64 |
TINYINT [UNSIGNED] | 带符号的范围-128~127,无符号范围0~255。默认有符号 | |
BOOL | 使用0和1表示真假 | |
SMALLINT [UNSIGNED] | 带符号是-2^15~2^15 - 1,无符号是2^16 - 1 | |
INT [UNSIGNED] | 带符号是-2^31~2^31 - 1,无符号是2^32 - 1 | |
BIGINT [UNSIGNED] | 带符号是-2^63~2^63 - 1,无符号是2^64 - 1 | |
FLOAT[(M, D)] [UNSIGNED] | M指定显示长度,D指定小数位数,占用四字节 | |
DOUBLE[(M, D)] [UNSIGNED] | 表示比float精度更高的小数,占用八字节 | |
DECIMAL[(M, D)] [UNSIGNED] | M指定长度,D表示小数位数 | |
文本、二进制类型 | CHAR(SIZE) | 固定长度字符串,最大255 |
VARCHAR(SIZE) | 可变长度字符串,最大长度65535 | |
BLOB | 二进制数据 | |
TEXT | 大文本,不支持全文索引,不支持默认值 | |
时间日期 | DATA/DATETIME/TIMESTAMP | 日期类型(yyyy-mm-dd)(yyyy-mm-dd hh:mm:ss),TIMESTAMP时间戳 |
String类型 | ENUM类型 | ENUM是一个字符串对象,其值来自表创建时在列规定中显示枚举的一个列值 |
SET类型 | SET是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个set成员的set列值时各成员之间用逗号隔开。这样set成员值本身不能包含逗号 |
数值类型
类型 | 字节数 | 最小值 | 最大值 | |
TINYINT | 有符号 | 1 | -128 | 127 |
无符号 | 1 | 0 | 255 | |
SMALLINT | 有符号 | 2 | -32768 | 32767 |
无符号 | 2 | 0 | 65535 | |
MEDIUMINT | 有符号 | 3 | -8388608 | 8388607 |
无符号 | 3 | 0 | 16777215 | |
INT | 有符号 | 4 | -2147483648 | 2147483647 |
无符号 | 4 | 0 | 4294967295 | |
BIGINT | 有符号 | 8 | -9223372036854775808 | 9223372036854775807 |
无符号 | 8 | 0 | 18446744073709551615 |
BIT类型
bit[(M)]:位字段类型。M表示每个值的位数,范围为1~64,默认值为1。
用法示例:
MariaDB [datatype]> create table bit_test(-> id int, -> num bit(8)-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into bit_test values(10, 10);
Query OK, 1 row affected (0.01 sec)MariaDB [datatype]> select * from bit_test;
+------+------+
| id | num |
+------+------+
| 10 | |
+------+------+
1 row in set (0.00 sec)
上述的bit(8)类型的10为什么不显示呢,因为bit字段在显示时,是按照ascii码对应的值来进行显示的。
MariaDB [datatype]> insert into bit_test values(65, 65);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from bit_test;
+------+------+
| id | num |
+------+------+
| 10 | |
| 65 | A |
+------+------+
2 rows in set (0.00 sec)
如果一个字段只需要存放0或1,可以定义bit(1),这样很节省空间。
MariaDB [datatype]> create table bit_test(-> num bit(1)-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into bit_test values(-1);
ERROR 1406 (22001): Data too long for column 'num' at row 1
MariaDB [datatype]> insert into bit_test values(0);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into bit_test values(1);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into bit_test values(2);
ERROR 1406 (22001): Data too long for column 'num' at row 1
MariaDB [datatype]> select * from bit_test;
+------+
| num |
+------+
| |
| |
+------+
2 rows in set (0.00 sec)
INT类型
有符号int越界测试:
MariaDB [datatype]> create table int_test(-> num int-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into int_test values(1);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into int_test values(2147483647);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into int_test values(2147483648);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
MariaDB [datatype]> select * from int_test;
+------------+
| num |
+------------+
| 1 |
| 2147483647 |
+------------+
2 rows in set (0.00 sec)
无符号int越界测试:
MariaDB [datatype]> alter table int_test modify num int unsigned;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0MariaDB [datatype]> insert into int_test values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
MariaDB [datatype]> insert into int_test values(4294967295);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into int_test values(4294967296);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
MariaDB [datatype]> select * from int_test;
+------------+
| num |
+------------+
| 1 |
| 2147483647 |
| 4294967295 |
+------------+
小数类型
float类型:
float[(m, d)] [unsigned]:m指定显示长度,d指定小数位数,占用四个空间。
float(4, 2)表示的范围是-99.99~99.99,MySQL在保存值时会进行四舍五入。
有符号float:
MariaDB [datatype]> create table float_test(-> num float(4, 2)-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into float_test values(-99.991);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into float_test values(99.99);
Query OK, 1 row affected (0.01 sec)MariaDB [datatype]> insert into float_test values(-99.99);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into float_test values(99.991);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from float_test;
+--------+
| num |
+--------+
| -99.99 |
| 99.99 |
| -99.99 |
| 99.99 |
+--------+
4 rows in set (0.00 sec)
无符号float:
MariaDB [datatype]> create table float_test(-> num float(4, 2) unsigned-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into float_test values(-99.99);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
MariaDB [datatype]> show warnings;
+-------+------+----------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------+
| Error | 1264 | Out of range value for column 'num' at row 1 |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)MariaDB [datatype]> insert into float_test values(99.99);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from float_test;
+-------+
| num |
+-------+
| 99.99 |
+-------+
1 row in set (0.00 sec)
decimal类型:
decimal(m, d) [unsigned]:m指定长度,d表示小数点的位数。
float和decimal的区别:
MariaDB [datatype]> create table float_decimal(-> `float` float(10, 8), -> `decimal` decimal(10, 8)-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into float_decimal values-> (1.23456789, 1.23456789);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from float_decimal;
+------------+------------+
| float | decimal |
+------------+------------+
| 1.23456788 | 1.23456789 |
+------------+------------+
1 row in set (0.00 sec)
注意:decimal和float很像,但是有区别,二者的精度是不同的,decimal的精度更高。
文本、二进制类型
char类型
char(size):固定长度字符串,size是可以存储的长度,单位为字符,最长为255。
示例:
MariaDB [datatype]> create table char_test(-> name char(2)-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into char_test values('he');
Query OK, 1 row affected (0.01 sec)MariaDB [datatype]> insert into char_test values('呵呵');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from char_test;
+--------+
| name |
+--------+
| he |
| 呵呵 |
+--------+
2 rows in set (0.00 sec)
注意:char(2)表示可以存放两个字符,可以是字母或汉字,但是不能超过2个,最多只能是255。
MariaDB [datatype]> create table char_test(-> name char(256)-> );
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead
varchar类型
varchar(size):可变长度字符串,size表示字符长度,最大长度为65535。
示例:
MariaDB [datatype]> create table varchar_test(-> name varchar(4)-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into varchar_test values('hehe');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into varchar_test values('我爱你!');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from varchar_test;
+------------+
| name |
+------------+
| hehe |
| 我爱你! |
+------------+
2 rows in set (0.00 sec)
注意:
varchar(size),这里的size到底有多大,这个size值,和表的编码相关。
- varchar长度可以指定为0~65535之间的值,但是有1~3个字节用于记录数据大小,所以说有效字节数是65532。
- 当表的编码方式是utf8时,varchar(size)的参数size最大值是65532 / 3 = 21844(因为utf8中,一个字符占用3个字节);如果是gbk编码,varchar(size)的参数size最大是65532 / 2 = 32766(因为gbk中,一个字符占用2个字节)。如果是utf8mb4时,varchar(size)的阐述size的最大值是65532 / 4 = 16383(因为utf8mb4中,椅子字符占用4个字节)。
MariaDB [datatype]> create table varchar_utf8(-> name varchar(65535)-> ) charset = utf8;
ERROR 1074 (42000): Column length too big for column 'name' (max = 21844); use BLOB or TEXT insteadMariaDB [datatype]> create table varchar_gbk(-> name varchar(65535)-> ) charset = gbk;
ERROR 1074 (42000): Column length too big for column 'name' (max = 32766); use BLOB or TEXT insteadMariaDB [datatype]> create table varchar_utf8mb4 (-> name varchar(65535)-> ) charset = utf8mb4;
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead
char和varchar的比较
实际存储 | char(4) | varchar(4) | char占用字节 | varchar占用字节 |
hehe | hehe | hehe | 4 * 3 = 12 | 4 * 3 + 1 = 13 |
S | S | S | 4 * 3 = 12 | 1 * 3 + 1 = 4 |
Shehe | × | × | 数据超过长度 | 数据超过长度 |
如何选择定长或变长字符串?
- 如果数据确定长度都一样,就使用定长(char),比如:手机号。
- 如果数据长度有变化,就使用变长(varchar),比如:地址,但是要保证最长的能存进去。
- 定长的比较浪费空间,但是效率高。
- 变长的比较节省空间,但是效率低。
日期和时间类型
常用的日期有如下三个:
- datetime:时间日期格式’yyyy-MM-dd HH:mm::ss’表示范围从1000~9999,占用8字节。不会进行时区的检索及转换。
- date:日期’yyyy-MM-dd’,占用3字节。
- timestamp:时间戳,从1970年开始的yyyy-MM-dd HH:mm:ss格式和datetime完全一致,占用4字节。表示范围从1970年到2038年,会自动检索当前时区并进行转换。
注意:yyyy-MM-dd HH:mm:ss中HH表示24小时制,如果是hh表示是12小时制。
示例:
MariaDB [datatype]> create table date_test(-> `date` date, -> `datetime` datetime, -> `timestamp` timestamp-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into date_test(`date`, `datetime`) values-> ('2018-12-3', '2018-12-3 9:50:30');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from date_test;
+------------+---------------------+---------------------+
| date | datetime | timestamp |
+------------+---------------------+---------------------+
| 2018-12-03 | 2018-12-03 09:50:30 | 2018-12-03 09:51:07 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
注意:添加数据时,时间戳自动补上当前时间,更新数据时,时间戳会自动更新成当前时间。
String类型
enum类型
enum('选项一', '选项二', '选项三'...);
enum提供若干个选项的值,最终在单元格中,实际只存储了其中一个,而且出于效率考虑,这些值实际存储的是’数字’,因为这些选项的每个选项值一次对应如下数字:1、2、3…最多65535;当我们添加枚举值时,也可以添加对应的数字编号。
示例:
MariaDB [datatype]> create table enum_test(-> gender enum('男', '女')-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into enum_test values('男');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into enum_test values('女');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into enum_test values(1);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into enum_test values(2);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from enum_test;
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 男 |
| 女 |
+--------+
4 rows in set (0.00 sec)
set类型
set('选项一', '选项二', '选项三', ...);
该设定只是提供了若干个选项的值,最终在一个单元格中,可以存储其中任意多个值,出于效率考虑,这些值实际存储的是’数字’,因为这些选项的每个选项值一次对应如下数字:1、2、4、8…最多64个。
示例:
MariaDB [datatype]> create table set_test(-> hobby set('爬山', '学习', '游戏', '追剧')-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into set_test values-> ('爬山,游戏,追剧');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into set_test values ('学习');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from set_test;
+----------------------+
| hobby |
+----------------------+
| 爬山,游戏,追剧 |
| 学习 |
+----------------------+
2 rows in set (0.00 sec)
集合查询函数find_in_set():
MariaDB [datatype]> select find_in_set('学习', '游戏,爬山,追剧,学习,睡觉');
+-------------------------------------------------------------+
| find_in_set('学习', '游戏,爬山,追剧,学习,睡觉') |
+-------------------------------------------------------------+
| 4 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
『SQL』数据类型
本篇博客介绍数据库中的常用数据类型。
数据类型分类
分类 | 数据类型 | 说明 |
数值类型 | BIT(M) | 位类型。M指定位数,默认值1,范围1~64 |
TINYINT [UNSIGNED] | 带符号的范围-128~127,无符号范围0~255。默认有符号 | |
BOOL | 使用0和1表示真假 | |
SMALLINT [UNSIGNED] | 带符号是-2^15~2^15 - 1,无符号是2^16 - 1 | |
INT [UNSIGNED] | 带符号是-2^31~2^31 - 1,无符号是2^32 - 1 | |
BIGINT [UNSIGNED] | 带符号是-2^63~2^63 - 1,无符号是2^64 - 1 | |
FLOAT[(M, D)] [UNSIGNED] | M指定显示长度,D指定小数位数,占用四字节 | |
DOUBLE[(M, D)] [UNSIGNED] | 表示比float精度更高的小数,占用八字节 | |
DECIMAL[(M, D)] [UNSIGNED] | M指定长度,D表示小数位数 | |
文本、二进制类型 | CHAR(SIZE) | 固定长度字符串,最大255 |
VARCHAR(SIZE) | 可变长度字符串,最大长度65535 | |
BLOB | 二进制数据 | |
TEXT | 大文本,不支持全文索引,不支持默认值 | |
时间日期 | DATA/DATETIME/TIMESTAMP | 日期类型(yyyy-mm-dd)(yyyy-mm-dd hh:mm:ss),TIMESTAMP时间戳 |
String类型 | ENUM类型 | ENUM是一个字符串对象,其值来自表创建时在列规定中显示枚举的一个列值 |
SET类型 | SET是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个set成员的set列值时各成员之间用逗号隔开。这样set成员值本身不能包含逗号 |
数值类型
类型 | 字节数 | 最小值 | 最大值 | |
TINYINT | 有符号 | 1 | -128 | 127 |
无符号 | 1 | 0 | 255 | |
SMALLINT | 有符号 | 2 | -32768 | 32767 |
无符号 | 2 | 0 | 65535 | |
MEDIUMINT | 有符号 | 3 | -8388608 | 8388607 |
无符号 | 3 | 0 | 16777215 | |
INT | 有符号 | 4 | -2147483648 | 2147483647 |
无符号 | 4 | 0 | 4294967295 | |
BIGINT | 有符号 | 8 | -9223372036854775808 | 9223372036854775807 |
无符号 | 8 | 0 | 18446744073709551615 |
BIT类型
bit[(M)]:位字段类型。M表示每个值的位数,范围为1~64,默认值为1。
用法示例:
MariaDB [datatype]> create table bit_test(-> id int, -> num bit(8)-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into bit_test values(10, 10);
Query OK, 1 row affected (0.01 sec)MariaDB [datatype]> select * from bit_test;
+------+------+
| id | num |
+------+------+
| 10 | |
+------+------+
1 row in set (0.00 sec)
上述的bit(8)类型的10为什么不显示呢,因为bit字段在显示时,是按照ascii码对应的值来进行显示的。
MariaDB [datatype]> insert into bit_test values(65, 65);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from bit_test;
+------+------+
| id | num |
+------+------+
| 10 | |
| 65 | A |
+------+------+
2 rows in set (0.00 sec)
如果一个字段只需要存放0或1,可以定义bit(1),这样很节省空间。
MariaDB [datatype]> create table bit_test(-> num bit(1)-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into bit_test values(-1);
ERROR 1406 (22001): Data too long for column 'num' at row 1
MariaDB [datatype]> insert into bit_test values(0);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into bit_test values(1);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into bit_test values(2);
ERROR 1406 (22001): Data too long for column 'num' at row 1
MariaDB [datatype]> select * from bit_test;
+------+
| num |
+------+
| |
| |
+------+
2 rows in set (0.00 sec)
INT类型
有符号int越界测试:
MariaDB [datatype]> create table int_test(-> num int-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into int_test values(1);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into int_test values(2147483647);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into int_test values(2147483648);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
MariaDB [datatype]> select * from int_test;
+------------+
| num |
+------------+
| 1 |
| 2147483647 |
+------------+
2 rows in set (0.00 sec)
无符号int越界测试:
MariaDB [datatype]> alter table int_test modify num int unsigned;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0MariaDB [datatype]> insert into int_test values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
MariaDB [datatype]> insert into int_test values(4294967295);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into int_test values(4294967296);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
MariaDB [datatype]> select * from int_test;
+------------+
| num |
+------------+
| 1 |
| 2147483647 |
| 4294967295 |
+------------+
小数类型
float类型:
float[(m, d)] [unsigned]:m指定显示长度,d指定小数位数,占用四个空间。
float(4, 2)表示的范围是-99.99~99.99,MySQL在保存值时会进行四舍五入。
有符号float:
MariaDB [datatype]> create table float_test(-> num float(4, 2)-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into float_test values(-99.991);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into float_test values(99.99);
Query OK, 1 row affected (0.01 sec)MariaDB [datatype]> insert into float_test values(-99.99);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into float_test values(99.991);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from float_test;
+--------+
| num |
+--------+
| -99.99 |
| 99.99 |
| -99.99 |
| 99.99 |
+--------+
4 rows in set (0.00 sec)
无符号float:
MariaDB [datatype]> create table float_test(-> num float(4, 2) unsigned-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into float_test values(-99.99);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
MariaDB [datatype]> show warnings;
+-------+------+----------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------+
| Error | 1264 | Out of range value for column 'num' at row 1 |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)MariaDB [datatype]> insert into float_test values(99.99);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from float_test;
+-------+
| num |
+-------+
| 99.99 |
+-------+
1 row in set (0.00 sec)
decimal类型:
decimal(m, d) [unsigned]:m指定长度,d表示小数点的位数。
float和decimal的区别:
MariaDB [datatype]> create table float_decimal(-> `float` float(10, 8), -> `decimal` decimal(10, 8)-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into float_decimal values-> (1.23456789, 1.23456789);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from float_decimal;
+------------+------------+
| float | decimal |
+------------+------------+
| 1.23456788 | 1.23456789 |
+------------+------------+
1 row in set (0.00 sec)
注意:decimal和float很像,但是有区别,二者的精度是不同的,decimal的精度更高。
文本、二进制类型
char类型
char(size):固定长度字符串,size是可以存储的长度,单位为字符,最长为255。
示例:
MariaDB [datatype]> create table char_test(-> name char(2)-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into char_test values('he');
Query OK, 1 row affected (0.01 sec)MariaDB [datatype]> insert into char_test values('呵呵');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from char_test;
+--------+
| name |
+--------+
| he |
| 呵呵 |
+--------+
2 rows in set (0.00 sec)
注意:char(2)表示可以存放两个字符,可以是字母或汉字,但是不能超过2个,最多只能是255。
MariaDB [datatype]> create table char_test(-> name char(256)-> );
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead
varchar类型
varchar(size):可变长度字符串,size表示字符长度,最大长度为65535。
示例:
MariaDB [datatype]> create table varchar_test(-> name varchar(4)-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into varchar_test values('hehe');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into varchar_test values('我爱你!');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from varchar_test;
+------------+
| name |
+------------+
| hehe |
| 我爱你! |
+------------+
2 rows in set (0.00 sec)
注意:
varchar(size),这里的size到底有多大,这个size值,和表的编码相关。
- varchar长度可以指定为0~65535之间的值,但是有1~3个字节用于记录数据大小,所以说有效字节数是65532。
- 当表的编码方式是utf8时,varchar(size)的参数size最大值是65532 / 3 = 21844(因为utf8中,一个字符占用3个字节);如果是gbk编码,varchar(size)的参数size最大是65532 / 2 = 32766(因为gbk中,一个字符占用2个字节)。如果是utf8mb4时,varchar(size)的阐述size的最大值是65532 / 4 = 16383(因为utf8mb4中,椅子字符占用4个字节)。
MariaDB [datatype]> create table varchar_utf8(-> name varchar(65535)-> ) charset = utf8;
ERROR 1074 (42000): Column length too big for column 'name' (max = 21844); use BLOB or TEXT insteadMariaDB [datatype]> create table varchar_gbk(-> name varchar(65535)-> ) charset = gbk;
ERROR 1074 (42000): Column length too big for column 'name' (max = 32766); use BLOB or TEXT insteadMariaDB [datatype]> create table varchar_utf8mb4 (-> name varchar(65535)-> ) charset = utf8mb4;
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead
char和varchar的比较
实际存储 | char(4) | varchar(4) | char占用字节 | varchar占用字节 |
hehe | hehe | hehe | 4 * 3 = 12 | 4 * 3 + 1 = 13 |
S | S | S | 4 * 3 = 12 | 1 * 3 + 1 = 4 |
Shehe | × | × | 数据超过长度 | 数据超过长度 |
如何选择定长或变长字符串?
- 如果数据确定长度都一样,就使用定长(char),比如:手机号。
- 如果数据长度有变化,就使用变长(varchar),比如:地址,但是要保证最长的能存进去。
- 定长的比较浪费空间,但是效率高。
- 变长的比较节省空间,但是效率低。
日期和时间类型
常用的日期有如下三个:
- datetime:时间日期格式’yyyy-MM-dd HH:mm::ss’表示范围从1000~9999,占用8字节。不会进行时区的检索及转换。
- date:日期’yyyy-MM-dd’,占用3字节。
- timestamp:时间戳,从1970年开始的yyyy-MM-dd HH:mm:ss格式和datetime完全一致,占用4字节。表示范围从1970年到2038年,会自动检索当前时区并进行转换。
注意:yyyy-MM-dd HH:mm:ss中HH表示24小时制,如果是hh表示是12小时制。
示例:
MariaDB [datatype]> create table date_test(-> `date` date, -> `datetime` datetime, -> `timestamp` timestamp-> );
Query OK, 0 rows affected (0.00 sec)MariaDB [datatype]> insert into date_test(`date`, `datetime`) values-> ('2018-12-3', '2018-12-3 9:50:30');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from date_test;
+------------+---------------------+---------------------+
| date | datetime | timestamp |
+------------+---------------------+---------------------+
| 2018-12-03 | 2018-12-03 09:50:30 | 2018-12-03 09:51:07 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
注意:添加数据时,时间戳自动补上当前时间,更新数据时,时间戳会自动更新成当前时间。
String类型
enum类型
enum('选项一', '选项二', '选项三'...);
enum提供若干个选项的值,最终在单元格中,实际只存储了其中一个,而且出于效率考虑,这些值实际存储的是’数字’,因为这些选项的每个选项值一次对应如下数字:1、2、3…最多65535;当我们添加枚举值时,也可以添加对应的数字编号。
示例:
MariaDB [datatype]> create table enum_test(-> gender enum('男', '女')-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into enum_test values('男');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into enum_test values('女');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into enum_test values(1);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into enum_test values(2);
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from enum_test;
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 男 |
| 女 |
+--------+
4 rows in set (0.00 sec)
set类型
set('选项一', '选项二', '选项三', ...);
该设定只是提供了若干个选项的值,最终在一个单元格中,可以存储其中任意多个值,出于效率考虑,这些值实际存储的是’数字’,因为这些选项的每个选项值一次对应如下数字:1、2、4、8…最多64个。
示例:
MariaDB [datatype]> create table set_test(-> hobby set('爬山', '学习', '游戏', '追剧')-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [datatype]> insert into set_test values-> ('爬山,游戏,追剧');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> insert into set_test values ('学习');
Query OK, 1 row affected (0.00 sec)MariaDB [datatype]> select * from set_test;
+----------------------+
| hobby |
+----------------------+
| 爬山,游戏,追剧 |
| 学习 |
+----------------------+
2 rows in set (0.00 sec)
集合查询函数find_in_set():
MariaDB [datatype]> select find_in_set('学习', '游戏,爬山,追剧,学习,睡觉');
+-------------------------------------------------------------+
| find_in_set('学习', '游戏,爬山,追剧,学习,睡觉') |
+-------------------------------------------------------------+
| 4 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
发布评论