Linux学习-数据类型学习

常用数据类型属性

ZEROFILL

#ZEROFILL对进行数据插入时定义了类型宽度，但没达到定义类型宽度的值进行补0显示
#使用ZEROFILL时，数据类型定义字段会自动加上UNSIGNED(无符号)
#8.0版本中已不再显示数据类型宽度字段，定义数据类型时，不需要定义
mysql> create table test_int2(f1 int,f2 int(5),f3 int(5) zerofill);
Query OK, 0 rows affected, 3 warnings (0.02 sec)

mysql> desc test_int2;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| f1    | int                      | YES  |     | NULL    |       |
| f2    | int                      | YES  |     | NULL    |       |
| f3    | int(5) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into test_int2 values(123456,123456,123456);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_int2 values(123456,123,123456);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_int2 values(123456,123,123);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_int2;
+--------+--------+--------+
| f1     | f2     | f3     |
+--------+--------+--------+
| 123456 | 123456 | 123456 |
| 123456 |    123 | 123456 |
| 123456 |    123 |  00123 |
+--------+--------+--------+
3 rows in set (0.01 sec)

整型类型适用场景及选择

TINYINT：一般用于枚举数据
SMALLINT：用于较小范围的统计数声，工厂库存
MEDIUMINT：用于较大整数的计算，车站客流量
INT/INTEGER：取值范围足够大，一般不用考虑超限，用得最多，比如商品编号
BIGINT：只有当你处理特别大的整数时才会用到，比如双十一，证券公司，大型门户网站点击量
#如何选择
考虑存储空间和可靠性的平衡性，一方面用占用字节较少的整数类型可以节省存储空间，另一方面为了节省存储空间，使得的整数类型取值范围太小，一旦遇到超出取值范围的情况，会引起系统错误，影响可靠性。
**系统故障产生的成本远远超过增加几个字段存储空间产生的成本**

FLOAT和DOUBLE

#因为浮点数是不准确的，所以应避免使用“=”来判断两个数是否相等
mysql> create table test_double2(f1 double);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_double2 values(0.47),(0.44),(0.19);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select sum(f1) from test_double2;
+--------------------+
| sum(f1)            |
+--------------------+
| 1.0999999999999999 |
+--------------------+
1 row in set (0.00 sec)

mysql> select sum(f1)=1.1,1.1=1.1 from test_double2;
+-------------+---------+
| sum(f1)=1.1 | 1.1=1.1 |
+-------------+---------+
|           0 |       1 |
+-------------+---------+
1 row in set (0.00 sec)
#通过DECIMAL解决浮点数精度问题
#DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，有效数据范围由M，D决定，存储空间不固守，由精度值M决定，总共占用的存储空间为M+2个字节，在精度要求不高的场景下，浮点数表达的数值范围更大一些，定点数在MySQL内部以字符串的形式存储，默认为DECIMAL(10,0)，当数据的精度超过了DECIMAL表示的精度时，则会进行四舍五入。
mysql> create table test_decimal2(f1 decimal);
mysql> desc test_decimal2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1    | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test_decimal2 values(0.47),(0.44),(0.19);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select sum(f1) from test_decimal2;
+---------+
| sum(f1) |
+---------+
|    1.10 |
+---------+
1 row in set (0.00 sec)

mysql> select sum(f1)=1.1 from test_decimal2;
+-------------+
| sum(f1)=1.1 |
+-------------+
|           1 |
+-------------+
#注：定点数取值范围小，但是精准，没有误差，适合于精度高的场景，浮点数相对于定点数的优点在长度一定的情况下，浮点类型取值范围更大，但是不精确，适用于需要取值范围大，可以容忍微小误差的科学计算场景(比如计算化学，分子建模，流体动力学等)

CHAR和VARCHAR

#char(M)固定长度，长度范围(0<=M<=255),占用M个字节
#varchar(M)可变长度，长度范围(0<=M<=65535)实际长度为(实际长度+1)个字节

#char(M)类型一般要预先定义字符串长度，如果不指定(M)，则表示默认是1个字符;如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定长度，当MySQL检索时，CHAR类型的字段会去除尾部的空格

mysql> create table test_char1(c1 char,c2 char(5));
Query OK, 0 rows affected (0.02 sec)

mysql> desc test_char1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | char(1) | YES  |     | NULL    |       |
| c2    | char(5) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test_char1(c1) values('a');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_char1;
+------+------+
| c1   | c2   |
+------+------+
| a    | NULL |
+------+------+
1 row in set (0.00 sec)
mysql> insert into test_char1(c1) values('ab');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
mysql> insert into test_char1(c2) values('小');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char1(c2) values('小丢丢');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char1(c2) values('小丢丢的爸爸');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql> insert into test_char1(c2) values('小丢丢爸爸');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_char1;
+------+-----------------+
| c1   | c2              |
+------+-----------------+
| a    | NULL            |
| NULL | 小              |
| NULL | 小丢丢          |
| NULL | 小丢丢爸爸      |
+------+-----------------+
4 rows in set (0.00 sec)

#VARCHAR(M)定义时，必须指定长度M，否则报错
MySQL4.0版本以下，varchar(20)指20字节，如果存放UTF8汉字时，只存放6个(每个汉字M[根据字符编码]个字节)
MySQL5.0版本以上，varchar(20)指20个字符
检索VARCHAR类型的字段数据时，会保留数据尾部的空格，VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节
#varchar(M),M的定义根据65535/字符需要占用的字节数(utf8mb4->4,utf8mb3->3)
mysql> create table test_varchar1(name varchar(65535));
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead

mysql> create table test_varchar1(name varchar(5));
Query OK, 0 rows affected (0.02 sec)

mysql> desc test_varchar1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(5) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test_varchar1 values("我是中国人");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_varchar1 values("我是中国人!");
ERROR 1406 (22001): Data too long for column 'name' at row 1


#varchar和char比较
#1.存储很短的信息，如门牌号101，201...这样很短的信息应使用char,因为varchar要占用一个字节存储信息长度，本来打算节约存储，结果得不偿失
#2.固定长度的，比如UUID为主键，那么使用char更合适，因长度固定，使用varchar还需要额外占用一个字节存储长度信息
#3.十分频繁改变的column,因为varchar每次存储都要有额外的计算，得到长度工作，频繁改变的字段要花费很多时间来计算
#4.具体存引擎中的情况
MyISAM数据表，最好使用固定长度的数据代替可变长度的数据列，这样使整个表静态化，从而使数据检索更快，用空间换时间。
MEMORY数据表目前使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都做CHAR处理。
INNODB数据表，建议使用VARCHAR类型，对于INNODB数据表，内部的行存储格式并没有区分固定长度和可变长度列，而且主要影响性能的因素是数据和使用的存储总量，则于char平均占用的空间多于varchar,所以除了简短并且固定长度的，其他考虑varchar,这样节省空间，对磁盘IO和数据存储总量较好。