ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySql基础：数据范例 [打印本页]

---

作者: 南飓风    时间: 2024-11-30 13:06
标题: MySql基础：数据范例
目录

1. 数据范例的整体分类
2. 整数数据范例
 2.1 TINYINT 范例
2.2 bit 范例 
2.3 小数范例
 2.3.1 float
2.3.2 decimal
2.4 字符串范例
2.4.1 char
2.4.3 varchar
2. 5 日期和时间范例

2.6 enum和set范例
 补充：

---

1. 数据范例的整体分类

        阐明：在mysql表中创建属性列都是 列名称在前 范例在后。

2. 整数数据范例

        阐明：在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。可以通过UNSIGNED来阐明某个字段是无符号的

范例	字节	最小值	最大值
		（带符号的/无符号的)	（带符号的/无符号的)
TINYINT	1	-128	127
UNSIGNED		0	255
SMALLNT	2	-32768	32767
UNSIGNED		0	65535
MEDIUMINT	3	-8388608	8388607
UNSIGNED		0	1677215
INT	4	-2147483648	2147483647
UNSIGNED		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
UNSIGNED		0	18446744073709551615

 2.1 TINYINT 范例

        阐明：尽量不使用unsigned，对于int范例大概存放不下的数据，int unsigned同样大概存放不 下，与其云云，还不如设计时，将int范例提拔为bigint范例。

1. 有符号越界测试案例：
2. mysql> create table test1(num tinyint);
3. Query OK, 0 rows affected (0.02 sec)
5. mysql> insert into test1 values(-128);
6. Query OK, 1 row affected (0.00 sec)
8. mysql> insert into test1 values(127);
9. Query OK, 1 row affected (0.00 sec)
11. mysql> insert into test1 values(128);
12. ERROR 1264 (22003): Out of range value for column 'num' at row 1

复制代码

         阐明：如果我们向MySQL特定的范例中插入不合法的数据，MYSQL一般都是直接拦截不让我们对应操纵!反之如果我们有数据已经插入到MySQL中，那么数据肯定是合法的。简而言之，这也是对数据范例的一种约束！

1. 无符号测试案例：
2. mysql> insert into test2 values(0);
3. Query OK, 1 row affected (0.00 sec)
5. mysql> insert into test2 values(255);
6. Query OK, 1 row affected (0.00 sec)
8. mysql> insert into test2 values(256);
9. ERROR 1264 (22003): Out of range value for column 'num' at row 1

复制代码

2.2 bit 范例 

   语法： bit[(M)] : 位字段范例。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1

1. 一个字节插入测试：
2. mysql> insert test1 values(1,1);
3. Query OK, 1 row affected (0.01 sec)
5. mysql> insert test1 values(1,0);
6. Query OK, 1 row affected (0.01 sec)
8. mysql> insert test1 values(1,2);
9. ERROR 1406 (22001): Data too long for column 'num' at row 1

复制代码

        阐明：当bit的位数位1时只能插入0/1，插入其他则报错 。

1. 测试：
2. mysql> select *from test1;
3. +------+------------+
4. | id   | num        |
5. +------+------------+
6. |    1 | 0x01       |
7. |    1 | 0x00       |
8. +------+------------+
9. 2 rows in set (0.00 sec)

复制代码

       
2.3 小数范例

 2.3.1 float

   语法：float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节
           阐明：假如float的指定长度为4，那么表示的范围则为-99.99~99.99，同时MYSQL会在保存时四舍五入。

1. 测试案例：
2. mysql> create table test1 (id int ,salary float(4,2));
3. Query OK, 0 rows affected, 1 warning (0.02 sec)
5. mysql> insert test1 values(1,99.99);
6. Query OK, 1 row affected (0.01 sec)
8. mysql> insert test1 values(2,0);
9. Query OK, 1 row affected (0.01 sec)
11. mysql> insert test1 values(3,-1);
12. Query OK, 1 row affected (0.01 sec)
14. mysql> select *from test1;
15. +------+--------+
16. | id   | salary |
17. +------+--------+
18. |    1 |  99.99 |
19. |    2 |   0.00 |
20. |    3 |  -1.00 |
21. +------+--------+
22. 3 rows in set (0.00 sec)

复制代码

        阐明：固然MYSQL会检查数据的范例，如果超出范围则会报错，但对于float范例，会自动的进行四舍五入后再检察时候超出范围。

        阐明：如果定义的float（4，2）是unsigned时，范围则为0-99.99.

1. 无符号测试：
2. mysql> insert test2 values(1,99.99);
3. Query OK, 1 row affected (0.00 sec)
5. mysql> insert test2 values(2,0);
6. Query OK, 1 row affected (0.01 sec)
8. mysql> insert test2 values(3,-1);
9. ERROR 1264 (22003): Out of range value for column 'salary' at row 1
10. mysql> insert test2 values(4,-0.1);
11. ERROR 1264 (22003): Out of range value for column 'salary' at row 1

复制代码

2.3.2 decimal

        阐明：float表示的精度大约为7位，所以7位以后则会产生精度丢失。而decimal的精度则远大于float，所以如果插入的数据需要高精度则可以使用decimal。至于decimal的使用法和float大致相同。
   语法：decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

1. 测试：
2. mysql> desc test4;
3. +-------+---------------+------+-----+---------+-------+
4. | Field | Type          | Null | Key | Default | Extra |
5. +-------+---------------+------+-----+---------+-------+
6. | num1  | float(10,8)   | YES  |     | NULL    |       |
7. | num2  | decimal(10,8) | YES  |     | NULL    |       |
8. +-------+---------------+------+-----+---------+-------+
9. 2 rows in set (0.00 sec)
11. mysql> insert test4 values(20.12345678,20.12345678);
12. Query OK, 1 row affected (0.00 sec)
14. mysql> select *from test4;
15. +-------------+-------------+
16. | num1        | num2        |
17. +-------------+-------------+
18. | 20.12345695 | 20.12345678 |
19. +-------------+-------------+
20. 1 row in set (0.00 sec)

复制代码

        阐明：如上代码所示，同样插入相同的数据，float范例会自动调解，而decimal范例则会保持原来的精度。

2.4 字符串范例

2.4.1 char

   语法：char(L): 固定长度字符串，L是可以存储的长度，单元为字符（和以往所认为的一个字符等同于一个字节不一样，在MySQL中一个字符可以是一个数字、一个汉字、字母等），最大长度值可以为255

1. 测试：
2. mysql> create table test1(id int ,name char(2));
3. Query OK, 0 rows affected (0.03 sec)
5. mysql> desc test1;
6. +-------+---------+------+-----+---------+-------+
7. | Field | Type    | Null | Key | Default | Extra |
8. +-------+---------+------+-----+---------+-------+
9. | id    | int     | YES  |     | NULL    |       |
10. | name  | char(2) | YES  |     | NULL    |       |
11. +-------+---------+------+-----+---------+-------+
12. 2 rows in set (0.00 sec)
14. mysql> insert test1 values(1,'a');
15. Query OK, 1 row affected (0.01 sec)
17. mysql> insert test1 values(2,'ab');
18. Query OK, 1 row affected (0.01 sec)
20. mysql> insert test1 values(3,'abc');
21. ERROR 1406 (22001): Data too long for column 'name' at row 1
22. mysql> insert test1 values(3,'中国');
23. Query OK, 1 row affected (0.01 sec)
25. mysql> insert test1 values(4,'去上学');
26. ERROR 1406 (22001): Data too long for column 'name' at row 1
27. mysql> select *from test1;
28. +------+--------+
29. | id   | name   |
30. +------+--------+
31. |    1 | a      |
32. |    2 | ab     |
33. |    3 | 中国   |
34. +------+--------+
35. 3 rows in set (0.00 sec)

复制代码

         阐明：char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255。

1. 测试：
2. mysql> create table test2(id int ,name char(255));
3. Query OK, 0 rows affected (0.02 sec)
5. mysql> desc test2;
6. +-------+-----------+------+-----+---------+-------+
7. | Field | Type      | Null | Key | Default | Extra |
8. +-------+-----------+------+-----+---------+-------+
9. | id    | int       | YES  |     | NULL    |       |
10. | name  | char(255) | YES  |     | NULL    |       |
11. +-------+-----------+------+-----+---------+-------+
12. 2 rows in set (0.00 sec)
14. mysql> create table test3 (id int ,name char(256));
15. ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

复制代码

2.4.3 varchar

        阐明：char和varchar最大的区别就是，varchar是用多少给多少，而char在最开始就已经确定。对于char和varchar的上限都是一样确定的。
   语法：varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

1. 测试：
2. mysql> create table test1 (id int ,name varchar(6));
3. Query OK, 0 rows affected (0.03 sec)
5. mysql> insert test1 values(1,'abcdef');
6. Query OK, 1 row affected (0.01 sec)
8. mysql> insert test1 values(1,'123456');
9. Query OK, 1 row affected (0.00 sec)
11. mysql> insert test1 values(1,'我不想去上学');
12. Query OK, 1 row affected (0.00 sec)
14. mysql> select *from test1;
15. +------+--------------------+
16. | id   | name               |
17. +------+--------------------+
18. |    1 | abcdef             |
19. |    1 | 123456             |
20. |    1 | 我不想去上学       |
21. +------+--------------------+
22. 3 rows in set (0.00 sec)

复制代码

        阐明：关于varchar(len),len到底是多大，这个len值，和表的编码密切相关： 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. mysql> create table test2 (name varchar(21845))charset=utf8;
3. ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
4. mysql> create table test (name varchar(21844))charset=utf8
5.     -> ;
6. Query OK, 0 rows affected, 1 warning (0.03 sec)

复制代码

那么如何选择定长或变长字符串？
        如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
        如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
        定长的磁盘空间比较浪费，但是效率高。
        变长的磁盘空间比较节省，但是效率低。
        定长的意义是，直接开辟好对应的空间 变长的意义是，在不超过自定义范围的环境下，用多少，开辟多少。

2. 5 日期和时间范例

        阐明：常见的日期和时间范例有以下三个
        date :日期 'yyyy-mm-dd' ，占用三字节
        datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表树模围从 1000 到 9999 ，占用八字节        
         timestamp ：时间戳，自动记载插入大概修改数据时最新的时间日期格式和datetime一致，占用四个字节。
   时间戳列可以有四张组合定义，其寄义分别为：
          1.当字段定义为timestamp，该字段在插入和更新时都不会自动设置为当前时间。
  
          2. 当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP，该字段仅在插入且未指定值时被赋予当前时间，再更新时且未指定值时不做修改。
  
          3. 当字段定义为timestamp ON UPDATE CURRENT_TIMESTAMP，该字段在插入且未指定值时被赋值为"0000-00-00 00:00:00",在更新且未指定值时更新为当前时间。
  
          4. 当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，该字段在插入或更新时未指定值，则被赋值为当前时间。
           阐明：当字段定义为timestamp，该字段在插入和更新时都不会自动设置为当前时间。

1. 测试：
2. mysql> creat table test (t1 date,t2 datetime,t3 timestamp);
3. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat table test (t1 date,t2 datetime,t3 timestamp)' at line 1
4. mysql> create table test (t1 date,t2 datetime,t3 timestamp);
5. Query OK, 0 rows affected (0.02 sec)
7. mysql> insert test(t1,t2)values('1992-02-20','1992-02-20');
8. Query OK, 1 row affected (0.01 sec)
10. mysql> insert test(t1,t2)values('1992-02-20','1992-02-21');
11. Query OK, 1 row affected (0.01 sec)
13. mysql> select*from test;
14. +------------+---------------------+------+
15. | t1         | t2                  | t3   |
16. +------------+---------------------+------+
17. | 1992-02-20 | 1992-02-20 00:00:00 | NULL |
18. | 1992-02-20 | 1992-02-21 00:00:00 | NULL |
19. +------------+---------------------+------+
20. 2 rows in set (0.00 sec)

复制代码

        阐明： 当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP，该字段仅在插入且未指定值时被赋予当前时间，再更新时且未指定值时不做修改。

1. 测试：
2. mysql> create table test1 (d1 int ,t1 timestamp default current_timestamp);
3. Query OK, 0 rows affected (0.02 sec)
5. mysql> desc test1;
6. +-------+-----------+------+-----+-------------------+-------------------+
7. | Field | Type      | Null | Key | Default           | Extra             |
8. +-------+-----------+------+-----+-------------------+-------------------+
9. | d1    | int       | YES  |     | NULL              |                   |
10. | t1    | timestamp | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
11. +-------+-----------+------+-----+-------------------+-------------------+
12. 2 rows in set (0.01 sec)
13. mysql> insert test1(d1)values(1);
14. Query OK, 1 row affected (0.00 sec)
16. mysql> select *from test1;
17. +------+---------------------+
18. | d1   | t1                  |
19. +------+---------------------+
20. |    1 | 2024-10-09 14:46:19 |
21. +------+---------------------+
22. 1 row in set (0.01 sec)
24. mysql> update test1 d1=2 where d1=1;
25. mysql> update test1 set d1=2 where d1=1;
26. Query OK, 1 row affected (0.00 sec)
27. Rows matched: 1  Changed: 1  Warnings: 0
29. mysql> select*from test1;
30. +------+---------------------+
31. | d1   | t1                  |
32. +------+---------------------+
33. |    2 | 2024-10-09 14:46:19 |
34. +------+---------------------+
35. 1 row in set (0.00 sec)

复制代码

        阐明：当字段定义为timestamp ON UPDATE CURRENT_TIMESTAMP，该字段在插入且未指定值时被赋值为"0000-00-00 00:00:00",在更新且未指定值时更新为当前时间。

1. 测试：
2. mysql> create table test2(id int ,t1 timestamp on update current_timestamp);
3. Query OK, 0 rows affected (0.02 sec)
5. mysql> insert test2 (id)values(1);
6. Query OK, 1 row affected (0.01 sec)
8. mysql> select*from test2;
9. +------+------+
10. | id   | t1   |
11. +------+------+
12. |    1 | NULL |
13. +------+------+
14. 1 row in set (0.01 sec)
16. mysql> update test2 set id=2 where id=1;
17. Query OK, 1 row affected (0.00 sec)
18. Rows matched: 1  Changed: 1  Warnings: 0
20. mysql> select  *from test2;
21. +------+---------------------+
22. | id   | t1                  |
23. +------+---------------------+
24. |    2 | 2024-10-09 14:52:20 |
25. +------+---------------------+
26. 1 row in set (0.00 sec)

复制代码

        阐明：当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，该字段在插入或更新时未指定值，则被赋值为当前时间。

1. 测试：
2. mysql> create table test3(id int ,t1 timestamp default current_timestamp on update current_timestamp);
3. Query OK, 0 rows affected (0.02 sec)
5. mysql> insert test3 (id)values(1);
6. Query OK, 1 row affected (0.01 sec)
8. mysql> select *from test3;
9. +------+---------------------+
10. | id   | t1                  |
11. +------+---------------------+
12. |    1 | 2024-10-09 14:56:37 |
13. +------+---------------------+
14. 1 row in set (0.01 sec)
16. mysql> update test3 set id=2 where id=1;
17. Query OK, 1 row affected (0.00 sec)
18. Rows matched: 1  Changed: 1  Warnings: 0
20. mysql> select *from test3;
21. +------+---------------------+
22. | id   | t1                  |
23. +------+---------------------+
24. |    2 | 2024-10-09 14:57:00 |
25. +------+---------------------+
26. 1 row in set (0.00 sec)

复制代码

2.6 enum和set范例

        阐明：enum范例设定只是提供了多少个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率思量，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。
   语法：enum：枚举，“单选”范例；
          enum('选项1','选项2','选项3',...);

1. 测试：
2. mysql> create table test1(id int ,sex enum('男','女'));
3. Query OK, 0 rows affected (0.02 sec)
5. mysql> desc test1;
6. +-------+-------------------+------+-----+---------+-------+
7. | Field | Type              | Null | Key | Default | Extra |
8. +-------+-------------------+------+-----+---------+-------+
9. | id    | int               | YES  |     | NULL    |       |
10. | sex   | enum('男','女')   | YES  |     | NULL    |       |
11. +-------+-------------------+------+-----+---------+-------+
12. 2 rows in set (0.00 sec)
14. mysql> insert test1 values(1,'男');
15. Query OK, 1 row affected (0.00 sec)
17. mysql> insert test1 values(2,'女');
18. Query OK, 1 row affected (0.00 sec)
20. mysql> insert test1 values(3,1);
21. Query OK, 1 row affected (0.00 sec)
23. mysql> insert test1 values(4,2);
24. Query OK, 1 row affected (0.01 sec)
26. mysql> select *from test1;
27. +------+------+
28. | id   | sex  |
29. +------+------+
30. |    1 | 男   |
31. |    2 | 女   |
32. |    3 | 男   |
33. |    4 | 女   |
34. +------+------+
35. 4 rows in set (0.00 sec)

复制代码

         阐明：set设定只是提供了多少个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率思量，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，....最多64个。
   语法：set：集合，“多选”范例；
          set('选项值1','选项值2','选项值3', ...);

1. 测试：
2. mysql> create table test2 (id int ,hobby set('game','video','sports'));
3. Query OK, 0 rows affected (0.02 sec)
5. mysql> desc test2;
6. +-------+------------------------------+------+-----+---------+-------+
7. | Field | Type                         | Null | Key | Default | Extra |
8. +-------+------------------------------+------+-----+---------+-------+
9. | id    | int                          | YES  |     | NULL    |       |
10. | hobby | set('game','video','sports') | YES  |     | NULL    |       |
11. +-------+------------------------------+------+-----+---------+-------+
12. 2 rows in set (0.00 sec)
14. mysql> insert test2 values(1,'game');
15. Query OK, 1 row affected (0.00 sec)
17. mysql> insert test2 values(3,'game,video,sports');
18. Query OK, 1 row affected (0.01 sec)
20. mysql> insert test2 values(4,1);
21. Query OK, 1 row affected (0.00 sec)
23. mysql> insert test2 values(5,2);
24. Query OK, 1 row affected (0.00 sec)
26. mysql> insert test2 values(6,3);
27. Query OK, 1 row affected (0.00 sec)
29. mysql> select *from test2;
30. +------+-------------------+
31. | id   | hobby             |
32. +------+-------------------+
33. |    1 | game              |
34. |    2 | game,video        |
35. |    3 | game,video,sports |
36. |    4 | game              |
37. |    5 | video             |
38. |    6 | game,video        |
39. +------+-------------------+
40. 6 rows in set (0.00 sec)

复制代码

 补充：

        阐明：当使用枚举范例后，有些数据列会有多个选项值，那么在查找的时候如何找到所有对应的数据。
   语法：find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0； str_list 用逗号分隔的字符串。

1. 测试：
2. mysql> select *from test2;
3. +------+-------------------+
4. | id   | hobby             |
5. +------+-------------------+
6. |    1 | game              |
7. |    2 | game,video        |
8. |    3 | game,video,sports |
9. |    4 | game              |
10. |    5 | video             |
11. |    6 | game,video        |
12. +------+-------------------+
13. 6 rows in set (0.00 sec)
15. mysql> select *from test2 where find_in_set('video',hobby);
16. +------+-------------------+
17. | id   | hobby             |
18. +------+-------------------+
19. |    2 | game,video        |
20. |    3 | game,video,sports |
21. |    5 | video             |
22. |    6 | game,video        |
23. +------+-------------------+
24. 4 rows in set (0.00 sec)

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4