一、mysql
数据类型
mysql
支持的所有的数据类型。
1.1. 字符串类型
char(1)
是固定长度,我们设置长度是多少,只能输入多少。长度可以再0-255
的任意值。
varchar
类型的长度是可变的,例如,在创建表时指定了最大长度是100
,当我们存储数据之后可能不够100
个字符串,那么对于varchar
时,使用多少分配多少, varchar
类型实际占用的空间为字符串的实际长度加一。这样,即可有效节约系统的空间。
但是我们还要注意,当我们varchar(5)
的时候,其实是在所占用的字节数的实际长度的基础上家1
,最后一个字符串的结束标识符占用了一个字节。
另外varchar
类型将会hello_
最后面的空格保留着,而char
类型中hello_
后面的空格自动删除掉。(_
标识空格)。
最后我们需要对blob
和text
做一个简单的认识:
-
text
值对大小写不敏感,可以将text
列视为varchar
列,blob
和text
长度也是安装实际占用的; -
blob
可以存储图片,text
不行,只可以存储纯文本文件。 - 当保存或检索BLOB和TEXT列的值时不删除尾部空格 。
注意:我们需要慎用这两个字段,mysql
会将这两个类型的值会当做一个独立的对象处理,会建立一个单独的区域进行存储,每个值会采用1~4个指针,指向实际存储位置;并且如果对这两个字段进行排序,只能针对最前面的max_sort_length
。 如果只需要对前面的更少的字节进行排序,那么可以通过设置max_sort_length
参数或者substring(value,length)
来截取部分字符串; 创建临时表的情况下,因为如果临时表大小超过max_heap_table_size
或者tmp_table_size
,就会将临时表存储在磁盘上,进而导致整体速度下降 。
1.2. 数值型类型
int/integer
:当我们建表的时候:
create table table_name (
id int,
......
)
我们设置字段是int/integer
类型时,默认是带符号型数据,数据范围是(-2147483648, 2147483647)
,这是我们在去设置int(4)
设置int
类型的长度是没啥用处的,而设置长度时候,并不是控制我们输入数据的大小和输入数据的长度,因为当我们设置类型为int/integer
时,默认的长度就是11
;所以那么设置int/integer(5)
这种又啥用呢?
其实是因为我们使用的类型不对,我们需要这样设置属性:
create table table_name (
id int(3) unsigned zerofill,
......
)
这时候我们设置的int(3)
才会其中用的,当我们填充字段的时候输入1
的时候,实际上会出现001
这样。这样的意味着,当我们输入的数据不够三位时就会用0
填充。另外如果设置无符号数据的时候id int(3) unsiend zerofill
,这样就可以了。
这里我们需要注意下float
、double
和decimal
三种类型:
float
:最大的精度是6
;double
:最大的精度是8
,如果长度超过会丢失精度。float
和double
是非标准类型,在数据库中保存近似值。建议使用decimal
,这种数据类型是一字符串形式保存数值的。
最后我们说一下decimal
类型,语法中column_name decimal(p, d)
,p
表示有效数字数的精度,范围是1~65
;d
表示小数点后面的位数,范围是0~30
。mysql
要求d
小于等于p
。
当我们设置decimal(5,2)
:取值范围是-999.99
到999.99
。
实例 | 结果 | 原因 |
| | 超出的部分四舍五入 |
| | 位数不够会补零 |
1000.00 | 报错 | 超出范围 |
1.3. 时间和日期类型
这些类型基本上就没事可说的了。
1.4. 枚举类型
enum
类型又称为枚举类型,在创建表时,enum
类型的取值范围就以列表的形式指定了。其基本形式如下:属性名 enum('值1','值2',...,'值n')
,其中属性名参数指定字段的名称,值n
参数表示列表中的第n个值,这些值末尾的空格将会被系统直接删除。
enum
类型最多可枚举65 535
个元素。
create table table_name (
sex enum('男', '女') ,
......
)
1.5. set
类型
set
类型最多枚举64
个元素。
set
是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个set
成员的set
列值时各成员之间用逗号(‘,’)
间隔开。所以set
成员值本身不能包含逗号。
create table table_name (
name set('1', '2', '3', '5') ,
......
)
二、MySQL
引擎
mysql
常用的引擎是三种:InnoDB
存储引擎、MyISAM存储引擎
和Memory存储引擎
。
2.1. InnoDB
存储引擎
InnoDB
给MySQL
的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。 后面版本的 MySQL
的默认存储引擎就是InnoDB
。
InnoDB
支持自增主键和外键。
InnoDB
中,创建的表的表结构存储在frm
文件。数据和索引存储在innodb_data_home_dir
和innodb_data_file_path
定义的表空间中。
InnoDB
的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差,占用的数据空间相对较大。
2.2. MyISAM
存储引擎
MyISAM
是MySQL
中常见的存储引擎,曾经是MySQL
的默认存储引擎。
MyISAM
的表存储成3个文件。文件的名字与表名相同。拓展名为frm
、myd
、myi
。其实,frm
文件存储表的结构;myd
文件存储数据,是myData
的缩写;myi
文件存储索引,是myIndex
的缩写。
MyISAM
的优势在于占用空间小,处理速度快。缺点是不支持事务的完整性和并发性。
2.3. Memory
存储引擎
memory
是MySQL
中一类特殊的存储引擎。它使用存储在内存中的内容来创建表,而且数据全部放在内存中。
每个基于memory
存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同,类型为frm
类型。该文件中只存储表的结构。而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。
memory
默认使用哈希索引。速度比使用B型树索引快。 想用B型树索引,可以在创建索引时指定。
memory
用到的很少,因为它是把数据存到内存中,如果内存出现异常就会影响数据。如果重启或者关机,所有数据都会消失。因此,基于memory
的表的生命周期很短,一般是一次性的。
三、存储引擎对比和选择
特性 | | | |
事物安全 | 支持 | 不支持 | 不支持 |
存储限制 | | 有限制 | 有限制 |
空间使用 | 高 | 低 | 低 |
内存使用 | 高 | 低 | 高 |
插入速度 | 低 | 高 | 高 |
外键支持 | 支持 | 不支持 | 不支持 |
***InnoDB
***:支持事务处理,支持外键,支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高(比如银行),要求实现并发控制(比如售票),那选择InnoDB
有很大的优势。如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB
,因为支持事务的提交(commit
)和回滚(rollback
)。
***MyISAM
***:插入数据快,空间和内存使用比较低。如果表主要是用于插入新记录和读出记录,那么选择MyISAM
能实现处理高效率。如果应用的完整性、并发性要求比 较低,也可以使用。
***MEMORY
***:所有的数据都在内存中,数据的处理速度快,但是安全性不高。如果需要很快的读写速度,对数据的安全性要求较低,可以选择MEMOEY
。它对表的大小有要求,不能建立太大的表。所以,这类数据库只使用在相对较小的数据库表。
同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理,可以选择InnoDB
。这个数据库中可以将查询要求比较高的表选择MyISAM
存储。如果该数据库需要一个用于查询的临时表,可以选择MEMORY
存储引擎。
(二、三部分参考文章链接,大家可以去自己看看)