【MySQL】第四章-数据类型

发表于 | 分类于
字数统计: 2k

第四章 数据类型

数据类型及分类

  在MySQL中有着类似于C语言的各种数据类型，他们分别有着不同的大小，适用于不同的数据表示情况。

数据类型

数值类型

数据类型

  在数值类型中与C语言基本类似，每个类型都有有符号与无符号两种，表示范围也在上表中有所描述，接下来我挑几个比较特殊的类型进行讲解。

tinyint类型

  tinyint能表示刚好一个字节大小的数值，与c语言中char类型十分类似，不过在MySQL中此类型作为数值类型而非字符类型出现。接下来看看MySQL中对于数值越界的表现。

1
2
3
4
5
6
7
MariaDB [student]> create table test 
(
    id tinyint  
);

MariaDB [student]> insert into test values(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

  很明显，MySQL提醒我们我们所插入的数值越界了。

  接下来我们定义一个无符号的tityint类型，并插入一个负数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [student]> create table test 
    -> (
    ->     id tinyint unsigned
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [student]> desc test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [student]> insert into test values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

  在这里对无符号插入负数也会显示越界。在MySQL中可以指定有符号与无符号，默认是有符号的。

bit类型

  bit(N)类型能表示任意位大小的数值，N的数值是1-64如果忽略N则默认是1。

  bit最为特殊的地方是它在显示的时候默认是按照字符型进行显示的，这和其他数值类型都不同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MariaDB [student]> create table test 
    -> (
    -> id int,
    -> Bit bit(8)
    -> );
Query OK, 0 rows affected (0.11 sec)

MariaDB [student]> insert into test values(10, 10), (65, 65);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [student]> select * from test;
+------+------+
| id   | Bit  |
+------+------+
|   10 |      |
|   65 | A    |
+------+------+
2 rows in set (0.00 sec)

  可以发现bit类型为10的显示无法出现，而65出现了A，由此则可以说明bit类型特殊的显示方式。同时，如果一个字段只需要显示0或1用bit(1)可以大大的减少空间。

float类型

  float(M, D)，M表示显示的长度，D表示小数的位数，MySQL会自动四舍五入。占用空间四个字节。

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [student]> insert into test values(99.991), (99.99);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [student]> select * from test;
+--------+
| salary |
+--------+
|  99.99 |
|  99.99 |
+--------+
2 rows in set (0.00 sec)

  MySQL自动将我们的数据多余的额不服进行四舍五入后再进行存储。

  同时float虽然是小数类型但是也同样拥有无符号类型。float(4,2)的范围就是0 ~ 99.99。

decimal类型

  decimal也是小数类型，其大体与float类似，但是decimal可以表示更大的精度，类似于C语言中的double型。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
MariaDB [student]> create table test 
    -> (
    ->     salary decimal(10,8),
    ->     salary2 float(10,8)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [student]> insert into test values(12.12345678, 12.12345678);
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> select * from test;
+-------------+-------------+
| salary      | salary2     |
+-------------+-------------+
| 12.12345678 | 12.12345695 |
+-------------+-------------+
1 row in set (0.01 sec)

  因此我们再要表示精度更高的数据时往往使用decimal类型。

  float表示的精度大约是7位。
decimal整数最大位数M为65。支持小数最大位数D是30。如果D被省略，默认为0。如果M被省略，默认是
10。

字符串类型

  注意在MySQL中字符串用''或者""标记均可，不做区分

char类型

  char(L)为固定长度字符串类型，最大长度可以是255。意思是说char类型的字段存放数据最多只能放255个字母或者汉字，这点与C语言有很大区别。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [student]> create table test 
    -> (
    -> name char(256)
    -> );
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead
MariaDB [student]> create table test  ( name char(255) );
Query OK, 0 rows affected (0.01 sec)

MariaDB [student]> insert into test values('张三');
Query OK, 1 row affected (0.08 sec)

MariaDB [student]> select * from test;
+--------+
| name   |
+--------+
| 张三   |
+--------+
1 row in set (0.00 sec)

varchar类型

  varchar(L)为可变长度字符串类型，最大长度为65535。
\
  其实varchar最大长度为多少与字符编码密切相关，varchar长度可以指定为0到65535之间的值，但是有1 - 3个字节用于记录数据大小，所以说有效字节数是65532。当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字
节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

1
2
MariaDB [student]> create table test ( name varchar(21845) )charset=utf8;
ERROR 1074 (42000): Column length too big for column 'name' (max = 21844); use BLOB or TEXT instead

  以上代码验证了utf8下确实长度不能超过21844。

  同时char与varchar之间关于定长和变长有着很大的区别。
数据类型

  如上表所示，通常我们在使用char类型时，定义多长的长度，实际使用时不会改变大小，但是varchar不同，会根据实际存储数据大小进行自动调节和变更，但是会多占用一个字节的大小，同时还很吃效率，因此实际使用中选择合适的类型是至关重要的。

日期类型和时间类型

  常用的时间类型有以下三个。

  datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从1000到9999，占用八字节。

  date:日期 ‘yyyy-mm-dd’，占用三字节。

  timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss格式和datetime完全一致，占用四字节。

  时间戳我们在C语言中也已经有所接触了，可以帮助我们自动显示当前的时间，但是时间戳也有大小限制。（貌似目前已经快到极限了）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
MariaDB [student]> create table test
    -> (
    ->     d1 date,
    ->     d2 datetime,
    ->     d3 timestamp
    -> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [student]> insert into test(d1,d2) values('1999-1-20', '1999-1-20 1:0:0');
Query OK, 1 row affected (0.01 sec)

MariaDB [student]> select * from test;
+------------+---------------------+---------------------+
| d1         | d2                  | d3                  |
+------------+---------------------+---------------------+
| 1999-01-20 | 1999-01-20 01:00:00 | 2019-01-19 09:51:26 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

  时间戳会自动帮我们补齐，取当前系统时间。

enum和set类型

  enum枚举类型，用于多选一。最多可添加65535个选项。

  set集合类型，用于多选多。最多可添加64个选项。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
MariaDB [student]> create table test
    -> (
    ->     username varchar(30),
    ->     hobby set('游戏','追番','搞比利'),
    ->     gender enum('男','女')
    -> );
Query OK, 0 rows affected (0.04 sec)

MariaDB [student]> insert into test values('张三','搞比利,追番','男');
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> insert into test values('李四','游戏,追番',2);
Query OK, 1 row affected (0.00 sec)

MariaDB [student]> select * from test;
+----------+------------------+--------+
| username | hobby            | gender |
+----------+------------------+--------+
| 张三     | 追番,搞比利      | 男     |
| 李四     | 游戏,追番        | 女     |
+----------+------------------+--------+
2 rows in set (0.00 sec)

  我们在给enum和set类型添加数据时可以选择使用数字选择选项的方式进行添加，但是不建议这种方式，因为十分不利于代码可读性。

1
2
3
4
5
6
7
MariaDB [student]> select * from test where hobby = '追番,搞比利';
+----------+------------------+--------+
| username | hobby            | gender |
+----------+------------------+--------+
| 张三     | 追番,搞比利      | 男     |
+----------+------------------+--------+
1 row in set (0.00 sec)

  我们同时可以使用以上方式找到集合中指定选项的所有数据。还可以利用find_in_set()函数进行查询。

1
2
3
4
5
6
7
8
MariaDB [student]> select * from test where find_in_set('追番',hobby);
+----------+------------------+--------+
| username | hobby            | gender |
+----------+------------------+--------+
| 张三     | 追番,搞比利      | 男     |
| 李四     | 游戏,追番        | 女     |
+----------+------------------+--------+
2 rows in set (0.01 sec)

  由此即可查询出所有hobby集合中包含追番的数据。

-------------本文结束感谢您的阅读!-------------
记录学习每一分，感谢您的赞助