请选择 进入手机版 | 继续访问电脑版
电路城论坛交流群:579145816
查看: 109|回复: 1

[经验分享] 基于MySQL和SQL Server语法中的数据类型和数据属性的分析

[复制链接]

签到天数: 87 天

[LV.6]签到达人

发表于 2018-9-14 17:30:42 | 显示全部楼层 |阅读模式
1. 数据类型
数据类型的范围是根据bit位的数量值来计算的。4字节的int占用32bit,所以可以表示的范围为0-2^32。
1.png

数值在存储(或调入内存)时,以数值型方式存储比字符型或日期时间类型更节省空间。在整数值存储上,0-255之间的任意整数都只占一个字节,256-65535之间的任意整数都占2个字节,而占用4个字节时便可以代表几十亿个整数之间的任意一个,这显然比字符型存储时每个字符占用一个字节节省空间的多。例如值"100"存储为字符型时占用三个字节,而存储为数值型将只占用一个字节。因此数据库默认将不使用引号包围的值当作数值型,如果明确要存储为字符型或日期时间型则应该使用引号包围以避免歧义。

值          CHAR(4)    存储需求    VARCHAR(4)    存储需求
-----------------------------------------------------------
''          '    '     4个字节     ''            1个字节
'ab'        'ab  '     4个字节     'ab '         3个字节
'abcd'      'abcd'     4个字节     'abcd'        5个字节
'abcdefgh'  'abcd'     4个字节     'abcd'        5个字节
MySQL在检索或操作char时会删除尾随空格,也就是说在where语句中name='gaoxiaofang   '和name='gaoxiaofang'的结果是一样的;若name='gaoxiaofang   ',那么concat(name,'x')的结果将是gaoxiaofangx。

而检索或操作varchar时不会删除尾随空格。但是char类型的列和varchar类型的列进行比较会忽略尾随空格,即 char:a=varchar:a。

mysql> create table test(a char(10),b varchar(10));
mysql> insert into test select 'a   ','a   ';
mysql> select concat(a,'x'),concat(b,'x'),a=b from test;
+---------------+---------------+-----+
| concat(a,'x') | concat(b,'x') | a=b |
+---------------+---------------+-----+
| ax            | a   x         |   1 |
+---------------+---------------+-----+
1 row in set
关于char(M)和varchar(M),其长度是M个字符(MySQL早期版本是M字节),其字节数和字符集有关,例如latain1字符集下char(30)表示能存储30个字符也就是30个字节,而utf8字符集下char(30)只能存储30个字符(哪怕是英文字母),但该列将占用30*3=90个字节的空间。

mysql> create table test9(a char(2) charset utf8mb4,b char(2)) charset=latain1;
mysql> insert into test9 values('我是','wo'),('wo','wo');
mysql> select length(a),char_length(a),length(b),char_length(b) from test9;
+-----------+----------------+-----------+----------------+
| length(a) | char_length(a) | length(b) | char_length(b) |
+-----------+----------------+-----------+----------------+
|         6 |              2 |         2 |              2 |
|         2 |              2 |         2 |              2 |
+-----------+----------------+-----------+----------------+
2 rows in set (0.00 sec)'
varchar(M)的字节数还和存储的字节数有关,每2^8次方字节增加一字节结束符。

关于日期时间的输入方式是非常宽松的,以下几种方式都是被允许的:任意允许的分隔符,建议使用4位的年份。

2011-01-01 18:40:20
2011/01/01 18-40-20
20110101184020
对于ENUM,插入数据时忽略大小写。如果enum列是允许NULL的,则NULL值也是有效值。

对于SET类型,和enum类似,不区分大小写,存储时删除尾随空格,null也是有效值。但不同的是可以组合多个给出的值。如set('a','b','c','d')可以存储'a,b','d,b'等,多个成员之间使用逗号隔开。所以,使用多个成员的时候,成员本身的值中不能出现逗号。并且存储数据时忽略重复成员并按照枚举时的顺序存储,如set('d','b','a'),存储'a,b,a','b,a,b'的结果都是'b,a'。使用find_in_set(set_value,set_column_name)可以检索出包含指定set值set_value的行。

SELECT * FROM test6 WHERE FIND_IN_SET('d',col)>0;

2. 数据类型属性

2.1 unsigned
unsigned属性就是让数值类型的数据变得无符号化。使用unsigned属性将会改变数值数据类型的范围,例如tinyint类型带符号的范围是-128到127,而使用unsigned时范围将变成0到255。同时unsigned也会限制该列不能插入负数值。

create table t(a int unsigned,b int unsigned);
insert into t select 1,2;
insert into t select -1,-2;
上面的语句中,在执行第二条语句准备插入负数时将会报错,提示超出范围。

使用unsigned在某些情况下确有其作用,例如一般的ID主键列不会允许使用负数,它相当于实现了一个check约束。

但是使用unsigned有时候也会出现些不可预料的问题:在进行数值运算时如果得到负数将会报错。例如上面的表t中,字段a和b都是无符号的列,且有一行"a=1,b=2"。

mysql> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set
此时如果计算"a-b"将会出错,不仅如此,只要是unsigned列参与计算并将得到负数都会出错。

mysql> select a-b from t;
1690 - BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'
mysql> select a-2 from t;
1690 - BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - 2)'
而不是负数的结果将不会有影响。

mysql> select 2-a,a*3 from t;
+-----+-----+
| 2-a | a*3 |
+-----+-----+
|   1 |   3 |
+-----+-----+
1 row in set
这并不是MySQL中的bug,在C语言中的unsigned也一样有类似的问题。这个问题在MySQL中设置set sql_mode='no_unsigned_subtraction'即可解决。


2.2 zerofill
zerofill修饰字段后,不足字段显示部分将使用0来代替空格填充,启用zerofill后将自动设置unsigned。zerofill一般只在设置了列的显示宽度后一起使用。关于列的显示宽度在上文已经介绍过了。

mysql> create table t1(id int(4) zerofill);
mysql> select * from t1;
+-------+
| id    |
+-------+
|  0001 |
|  0002 |
|  0011 |
| 83838 |
+-------+
4 rows in set (0.00 sec)
zerofill只是修饰显示结果,不会改变存储的数据值。

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

返回顶部