首页 > 编程入门 文章详情

『SQL』数据类型

编程入门 行业动态 更新时间:2024-10-20 06:31:05

『SQL』<a href=https://www.elefans.com/category/jswz/34/1768302.html style=数据类型"/>

『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-128127
无符号10255
SMALLINT有符号2-3276832767
无符号2065535
MEDIUMINT有符号3-83886088388607
无符号3016777215
INT有符号4-21474836482147483647
无符号404294967295
BIGINT有符号8-92233720368547758089223372036854775807
无符号8018446744073709551615

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占用字节
hehehehehehe4 * 3 = 124 * 3 + 1 = 13
SSS4 * 3 = 121 * 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』数据类型

本文发布于:2024-02-12 03:49:07，感谢您对本站的认可！
本文链接:https://www.elefans.com/category/jswz/34/1685749.html
版权声明:本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系，我们将在24小时内删除。
本文标签：数据类型   SQL

发布评论

评论列表 （有 0 条评论）

最近发表

草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍！

热门文章

标签列表