char 和 varchar 的区别
char:
- 定长字符串(最多 255),插入长度小于固定长度时,用空格填充;
- 因为长度固定,所以存取更快,甚至能快 50%,但也会占用更多空间;
varchar:
- 可变长字符串(最多65532),按数据长度存储。
MySQL 货币字段类型选择
用 DECIMAL,这里需要说出decimal和double、float类型的区别,主要是精度
MySQL 货币用 Decimal 和 Numric 类型表示,这两种类型被 MySQL 实现为同样的类型。他们被用于保存与货币有关的数据。
例如 salary DECIMAL(9,2),9(precision)代表将被用于存储值的总的小数位数,而 2(scale)代表将被用于存储小数点后的位数。存储在 salary 列中的值的范围是从-9999999.99 到 9999999.99。
DECIMAL 和 NUMERIC 值作为字符串存储,而不是作为二进制浮点数,以便保存那些值的小数精度。
之所以不使用 float 或者 double 的原因:因为 float 和 double 是以二进制存储的,所以有一定的误差。
SQL分类
SQL:Structured Query Language,结构化查询语言,包括DCMQ:定义、控制、操作(增删改)、查询(查)
DDL,Data Definition Language,数据定义语言,定义数据库对象,包括数据库、数据表和列、库和表结构的增删改
DCL,Control,数据控制语言,定义访问权限和安全级别。
DML,Manipulation,数据操作语言,增删改。
DQL,Query,数据查询语言,查
数据类型
MySQL 常用数据类型有四种:字符串、日期/时间、数值、二进制。
1. 字符型
主要包括:char,varchar,tinytext,mediumtext,text,longtext,enum,set。
(1)CHAR : 定长字符类型,最多 2000 字节
(2)VARCHAR2 :可变长度字符类型,最多 4000 个字节
(3)LONG : 大文本类型。最大 2 个 G
mysql类型 |
大小 |
用途 |
对应Java类 |
---|---|---|---|
char |
0-255 bytes |
定长字符串 (姓名、性别、学号) |
String |
varchar |
0-65535 bytes |
变长字符串(比上面更长一点的那种) |
String |
tinytext |
0-255 bytes |
比较短的那种文本数据(新闻速报的那种) |
String |
mediumtext |
0-16 777 215 bytes |
中等长度的文本数据(小作文) |
String |
text |
0-65 535 bytes |
长文本数据() |
String |
longtext |
0-4 294 967 295 bytes |
极大文本数据(论文) |
String |
2.数值型
主要包含下列几种数据类型:tinyint,smallint,mediumint,int,bigint,float,double,decimal。
NUMBER: 数值类型
NUMBER(5) 最大可以存的数为 99999
NUMBER(5,2) 最大可以存的数为 999.99
mysql类型 |
用途 |
对应Java类 |
---|---|---|
tinyint |
一个很小很小的整数 |
Integer |
smallint |
一个小整数 |
Integer |
mediumint |
一个中等大小的整数 |
Integer |
int |
一个int大小的整数 (大都用来做id) |
Integer |
bigint |
一个蛮大的整数(也常用来做id) |
Long |
float |
学生成绩、允许有误差的、单精度浮点数 |
Float |
double |
学生成绩、允许有误差的、双精度浮点数 |
Double |
decimal |
计算工资、盈利、金融方面 |
Java.math.BigDecimal |
3.日期型
主要包括:date,time,datetime,timestamp,year。
(1)DATE:日期时间型,精确到秒
(2)TIMESTAMP:精确到秒的小数点后 9 位
mysql类型 |
用途 |
---|---|
date |
YYYY-MM-DD格式的日期值 |
time |
hh:mm:ss格式的时间值 |
datetime |
YYYY-MM-DD hh:mm:ss格式的日期和时间值 |
timestamp |
YYYY-MM-DD hh:mm:ss格式的时间戳记值 |
year |
YYYY或YY格式的年值 |
这个时间的对应的Java类、只要格式正确、都是可以取的,主要看需求是什么。不管你后端是String、Date 类型、只要格式是对应的就是可以的。
4.二进制型(大数据类型)
主要包括:tityblob,blob,mediumblob,longblob。
(1)CLOB : 存储字符,最大可以存 4 个 G
(2)BLOB:存储图像、声音、视频等二进制数据,最多可以存 4 个 G
mysql类型 |
描述 |
---|---|
tityblob |
不超过 255 个字符的二进制字符串 |
blob |
二进制形式的长文本数据 |
mediumblob |
二进制形式的中等长度文本数据 |
longblob |
二进制形式的极大文本数据 |
MySQL int类型
对于 int 类型的一些基础知识其实上图已经说的很明白了。
int(11)
与varchar不同,11 代表的并不是长度,而是字符的显示宽度,无论 int 显示宽度设置为多少,能存储的最大值和最小值是固定的。
当 int 字段类型设置为无符号且填充零(UNSIGNED ZEROFILL)时,当数值位数未达到设置的显示宽度时,会在数值前面补充零直到满足设定的显示宽度,为什么会有无符号的限制呢,是因为 ZEROFILL 属性会隐式地将数值转为无符号型,因此不能存储负的数值。
参考以下几个结论:
1. 如果一个字段设置了无符号和填充零属性,那么无论这个字段存储什么数值,数值的长度都会与设置的显示宽度一致,如上述例子中的字段 b,插入数值 1 显示为00000000001,左边补了 10 个零直至长度达到 11 位;
2. 设置字段的显示宽度并不限制字段存储值的范围,比如字段 d 设置为 int(5),但是仍然可以存储 1234567890 这个 10 位数字;
3. 设置的字符宽度只对数值长度不满足宽度时有效,如 d 字段 int(5),插入 1 时,长度不足 5,因此在左边补充 4 个零直到 5 位,但是插入 1234567890 时超过了 5 位,这时的显示宽度就起不了作用了。
int(10) unsigned
如果我们创建一个INT的字段dataType并且没有指定任何长度/值,那么它会自动变为int(11),如果我们设置属性UNSIGNED或UNSIGNED ZEROFILL,那么它将转为int(10)
MySQL char、varchar、text的区别
1 长度:
char:0~255
varchar:最大64k(注意64k是整个row的长度,要考虑到其它列的长度,如果存在not null时也会占用一位,不同字符集有效长度还不一样,如utf8最多21845,但varchar在一般情况下都够用了)
text:最大4G,适合大文本。
2 效率:
一般char > varchar > text,如果是Innodb引擎,推荐用varchar代替char。
3 默认值:
char和varchar可以有默认值,text不能指定默认值。
对于int类型,如果不需要存取负值,最好加上unsigned;对于经常出现在where语句中的字段,考虑加索引,整形的尤其适合加索引。
MySQL unsigned / zerofill
即有符号(signed)、无符号(unsigned )、零填充(zerofill)
unsigned
mysql自定义的类型,表示无符号数值即非负数。signed为整型默认属性。
作用:约束数值+增加数值范围。
以tinyint为例,它的取值范围-128-127,加不加signed都默认此范围。加上unsigned表示范围0-255,其实相当于把负数那部分加到正数上。
如身高、体重、年龄等字段一般不会为负数,此时可以设置一个 UNSIGNED ,不允许负数插入。
CREATE TABLE test ( id TINYINT UNSIGNED NOT NULL, name VARCHAR(20) NOT NULL );
在上面的示例中,id字段被设置为TINYINT类型,并包含了unsigned属性。这意味着该字段只能存储0到255之间的值,而不包括任何负数。
unsigned仅限整型类型
五种整型(tinyint、smallint、mediumint、integer和bigint)
三种浮点型(float、double和decimal)
整型数值可定义为unsigned,使列的取值为0及以上范围。
浮点类型不能使用unsigned,其取值范围也与整型不同。以int和float为例:两者都是4个字节,32位。
整型 int =1个符号位+31个指数位,取值范围为2^-31——2^31-1。
但是浮点型float =1个符号位+8个指数位+23个尾数位,取值采用的是IEEE 754标准,具体的规则这里不做介绍了。
zerofill
零填充会自动使用无符号位。零填充指的是位数固定,如果数值长度不足字段类型的长度,则使用0来填充。比如zerofill 的int数值,设为123,数据库中会显示0000000123;
插入数据时,当该字段的值的长度小于定义的长度时,会在该值的前面补上相应的0。默认为int(10)
补充:unsigned可能带来的异常
使用unsigned可能会带来一些意想不到的效果,与其使用unsigned,还不如在数据库设计阶段将INT类型提升为BIGINT类型。
mysql学习笔记(三):unsigned理解以及特殊情况
https://www.cnblogs.com/isme-zjh/p/11542861.html
UNSIGNED
UNSIGNED属性就是将数字类型无符号化,与C、C++这些程序语言中的unsigned含义相同。例如,INT的类型范围是-2 147 483 648 ~ 2 147 483 647, INT UNSIGNED的范围类型就是0 ~ 4 294 967 295。
看起来这是一个不错的属性选项,特别是对于主键是自增长的类型,因为一般来说,用户都希望主键是非负数。然而在实际使用中,UNSIGNED可能会带来一些负面的影响,示例如下:
mysql> CREATE TABLE t ( a INT UNSIGNED, b INT UNSIGNED )
ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO t SELECT 1,2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 1
b: 2
1 row in set (0.00 sec)我们创建了一个表t,存储引擎为InnoDB。表t上有两个UNSIGNED的INT类型。输入(1,2)这一行数据,目前看来都没有问题,接着运行如下语句:
SELECT a - b FROM t这时结果会是什么呢?会是-1吗?答案是不确定的,可以是-1,也可以是一个很大的正值,还可能会报错。在Mac操作系统中,MySQL数据库提示如下错误:
mysql> SELECT a-b FROM t;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'这个错误乍看起来非常奇怪,提示BIGINT UNSIGNED超出了范围,但是我们采用的类型都是INT UNSIGNED啊!而在另一台Linux操作系统中,运行的结果却是:
mysql> SELECT a -b FROM t\G;
*************************** 1. row ***************************
a - b: 4294967295
1 row in set (0.00 sec)在发生上述这个问题的时候,有开发人员跑来和笔者说,他发现了一个MySQL的Bug,MySQL怎么会这么“傻”呢?在听完他的叙述之后,我写了如下的代码并告诉他,这不是MySQL的Bug,C语言同样也会这么“傻”。
#include
int main(){
unsigned int a;
unsigned int b;
a = 1;
b = 2;
printf(a - b: %d\n,a-b);
printf(a - b: %u\n,a-b);
return 1;
}上述代码的运行结果是:
a - b: -1
a - b: 4294967295可以看到,在C语言中a-b也可以返回一个非常巨大的整型数,这个值是INT UNSIGNED的最大值。难道C语言也发生了Bug?这怎么可能呢?
在实际的使用过程中,MySQL给开发人员的印象就是存在很多Bug,只要结果出乎预料或者有开发人员不能理解的情况发生时,他们往往把这归咎于MySQL的 Bug。和其他数据库一样,MySQL的确存在一些Bug,其实并不是MySQL数据库的Bug比较多,去看一下Oracle RAC的Bug,那可能就更多了,它可是Oracle的一款旗舰产品。因此,不能简单地认为这个问题是MySQL的Bug。
对于上述这个问题,正如上述所分析的,如果理解整型数在数据库中的表示方法,那么这些就非常好理解了,这也是为什么之前强调需要看一些计算机组成原理方面相关书籍的原因。将上面的C程序做一些修改:
#include
int main(){
unsigned int a;
unsigned int b;
a = 1;
b = 2;
printf(a - b: %d,%x\n,a-b,a-b);
printf(a - b: %u,%x\n,a-b,a-b);
return 1;
}这次不仅打印出a-b的结果,也打印出a-b的十六进制结果,运行程序后的结果如下所示:
a - b: -1,ffffffff
a - b: 4294967295,ffffffff可以看到结果都是0xFFFFFFFF,只是0xFFFFFFFF可以代表两种值:对于无符号的整型值,其是整型数的最大值,即4 294 967 295;对于有符号的整型数来说,第一位代表符号位,如果是1,表示是负数,这时应该是取反加1得到负数值,即-1。
这个问题的核心是,在MySQL数据库中,对于UNSIGNED数的操作,其返回值都是UNSIGNED的。而正负数这个问题在《MySQL技术内幕:InnoDB存储引擎》中有更深入的分析,有兴趣的可以进一步研究。
那么,怎么获得-1这个值呢?这并不是一件难事,只要对SQL_MODE这个参数进行设置即可,例如:
mysql>SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a-b FROM t\G;
*************************** 1. row ***************************
a-b: -1
1 row in set (0.00 sec)后面会对SQL_MODE进一步讨论,这里不进行深入的讨论。笔者个人的看法是尽量不要使用UNSIGNED,因为可能会带来一些意想不到的效果。另外,对于INT类型可能存放不了的数据,INT UNSIGNED同样可能存放不了,与其如此,还不如在数据库设计阶段将INT类型提升为BIGINT类型
本人遇到的类似问题:(linux上)
当(a-b)在where子句后时也会出现相同的情况
以下是php使用Mysql查询的结果(每组的第一行是第二行[1]-[2]的结果)
86374
a b
Array ( [1] => 1351843032 [2] => 1351756658 )
-2567
Array ( [1] => 1351843032 [2] => 1351845599 )
86374
Array ([1] => 1351843032 [2] => 1351756658 )
86374
Array ( [1] => 1351843032 [2] => 1351756658 )
-105849
Array ( [1] => 1351650809 [2] => 1351756658 )
86374
Array ( [1] => 1351843032 [2] => 1351756658 )
86374
Array ( [1] => 1351843032 [2] => 1351756658 )
下面在mysql语句中查询select * from table where (a-b)>86374;
结果(按正常思路来讲,结果应该为空,但在Linux是却现出以下结果 ):
Array ( [1] => 1351843032 [2] => 1351845599 )
Array ( [1] => 1351650809 [2] => 1351756658 )
而这个结果恰是[1]-[2]为负数的那两行。
结论:如果使用unsigne并且在where子句后出现两列相减值小于0((a-b)<0),在查询时,linux上的Mysql会将负数转换成unsigned后再进行查询( (-2576+4294967295+1)>86374, (-105849+4294967295+1)>86374 )。