数据类型
在MySQL里面我们将数据类型分为了以下一些类型:
1. 数值类型(整型、浮点)
2. 字符串类型
3. 日期时间类型
4. 复合类型
5. 空间类型(非科学性工作基本不用,不做讲解)
数值类型(整型、浮点)
整型
MySQL数据类型 | 所占字节 | 值范围 |
tinyint | 1字节 | -128~127 |
smallint | 2字节 | -32768~32767 |
mediumint | 3字节 | -8388608~8388607 |
int | 4字节 | -2147483648~2147483647 |
bigint | 8字节 | +-9.22*10的18次方 |
注意:
在创建表字段时,性别我们可以使用无符号的微小整型(tinyint)来表示。用0表示女、用1表示男。用2表示未知。
同样人类年龄也是,在创建表字段时可用用无符号的整型。因为人类的年龄还没有负数
在实际使用过程中。我们业务中最大需要存储多大的数值。我们创建表时,就选择什么样的类型来存储这样的值
浮点类型
MySQL数据类型 | 所占字节 | 值范围 |
float(m, d) | 4字节,单精度浮点型 | m总个数,d小数位 |
double(m, d) | 8字节,双精度浮点型 | m总个数,d小数位 |
decimal(m, d) | decimal是存储字符串的浮点数 |
注意:
浮点是非精确值,会存在不太准确的情况而decimal叫做定点数。在MySQL内部,本质上是用字符串存储的。实际使用过程中如果存在金额、钱精度要求比较高的浮点数存储,建议使用decimal(定点数 )这个类型
字符类型
MySQL数据类型 | 所占字节 | 值范围 |
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-255字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过255个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65535字节 | 二进制形式的长文本数据 |
TEXT | 0-65535字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 中等长度文本数据 |
MEDIUMTEXT | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LOGNGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
VARBINARY(M) | 允许长度0-M个字节的定长字节符串,值的长度+1个字节 | |
BINARY(M) | 允许长度0-M个字节的定长字节符串 | |
BINARY(M) | 允许长度0-M个字节的定长字节符串 |
CHAR
类型用于定长字符串,并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从
0-255。比指定长度大的值将被截短,而比指定长度小的值将会用空格作填补。
VARCHAR
把这个大小视为值的大小,在长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度,类型不会被空格填补,但长于指示器的值仍然会被截短。因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度,所以在不能确定字段需要多少字符时使用 VARCHAR类型可以大大地节约磁盘空间、提高存储效率。
text类型与blob类型
对于字段长度要求超过 255 个的情况下,MySQL 提供了 TEXT 和 BLOB两种类型。根据存储数据的大小,它们都有不同的子类型。这些大型的数据用于存储文本块或图像、 声音文件等二进制数据类型。TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写,而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。
时间类型
MySQL数据类型 | 所占字节 | 值范围 |
date | 3字节,日期 | 格式:2014-09-18 |
time | 3字节,时间 | 格式:08:42:30 |
datetime | 8字节,日期时间 | 格式:2014-09-18 08:42:30 |
timestamp | 4字节 | 自动存储记录修改的时间 |
year | 1字节 | 年份 |
注意:
时间类型在web系统中用的比较少, 很多时候很多人喜欢使用int来存储时间。插入时插入的是unix时间戳,因为这种方式更方便计算。在前端业务中用date类型的函数, 再将unix时间戳转成人们可识别的时间。上面的类型你可以根据实际情况实际进行选择 ,有些人为了在数据库管理中方便查看,也有人使用datetime类型来存储时间。
复合类型
MySQL数据类型 | 说明 | 举例 |
set | 集合类型 | set(“member”, “member2″, … “member64″) |
enum | 枚举类型 | enum(“member1″, “member2″, … “member65535″) |
注意
一个 ENUM 类型只允许从一个集合中取得一个值;而 SET 类型允许从一个集合中取得任意多个值
类型使用
我们学习了这么多类型,在创建表的语句的时候使用对应的类型即可
举例如下:
CREATE TABLE IF NOT EXISTS demo (
id int(11) NOT NULL,
username varchar(50) NOT NULL,
password char(32) NOT NULL,
content longtext NOT NULL,
createtime datetime NOT NULL,
sex tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
字段其他属性设置
UNSIGNED(无符号)
主要用于整型和浮点类型,使用无符号。即,没有前面的-(负号)。存储位数更长。
tinyint整型的取值区间为,-128~127。而使用无符号后可存储0-255个长度。
创建时在整型或浮点字段语句后接上:unsigned
ZEROFILL(0填充)
0(不是空格)可以用来真补输出的值。
使用这个修饰符可以阻止 MySQL 数据库存储负值。
创建时在整型或浮点字段语句后接上:zerofill
default
default属性确保在没有任何值可用的情况下,赋予某个常量值,
这个值必须是常量,因为MySQL不允许插入函数或表达式值。
此外,此属性无法用于BLOB或TEXT列。如果已经为此列指定了NULL属性,
没有指定默认值时默认值将为NULL,否则默认值将依赖于字段的数据类型。
创建时在整型或浮点字段语句后接上:default '值'
not null
如果将一个列定义为not null,将不允许向该列插入null值。
建议在重要情况下始终使用not null属性,因为它提供了一个基本验证,
创建时在整型或浮点字段语句后接上:not null
null
为列指定null属性时,该列可以保持为空, 而不论行中其它列是否已经被填充。
记住,null精确的说法是“无”,而不是空字符串或0。
创建时在整型或浮点字段语句后不要声明not null
即可。
字符集
1.字符集是什么?
为了更好的识别中文、日文、英文、希腊语。
对于常用的符号进行了编码,这个编码就是字符集.
字符集确定了文字的存储方式。
字符集相当于是计算机中人类的语言
2.字符集的重点知识
我们只需要了解:
- 常用字符集
- 数据库中我们用什么字符集
字符集 | 说明 | 字节长度 |
ASCII | 美国标准信息交换代码 | 单字节 |
GBK | 汉字内码扩展规范 | 双字节 |
unicode | 万国码 | 4字节 |
UTF-8 | Unicode的可变长度字符编码 | 1到6个字节 |
3.实际工作中要使用的编码
在中文中常用的字符集分为utf-8和GBK。实际使用的如下:
字符集 | 说明 |
gbk_chinese_ci | 简体中文, 不区分大小写 |
utf8_general_ci | Unicode (多语言), 不区分大小写 |
观察上图可知MySQL字符集由三个部分组成 :
1.字符集
2.语言
3.类型
最后的bin是指二进制字符集,后面的ci是指存储排序时不区分字符的大小写。
注意:
mysql在写utf-8的时候写的是utf8。不加中间的中横线。
表引擎
在mysql命令中使用:
show engines;可以查看到当前服务器支持的所有引擎
引擎名称 | 说明 |
MyISAM | 常用。读取效率很高的引擎 |
InnoDB | 常用。写入,支持事处等都支持 |
Archive | 不常用。归档引擎,压缩比高达1:10,用于数据归档 |
NDB | 不常用。主要在MySQL 集群服务器中使用,不做介绍 |
MyISAM
不支持事务,表锁(表级锁,加锁会锁住整个表), 支持全文索引,操作速度快。常用于读取多的业务。
myisam存储引擎表由myd和myi组成。.myd用来存放数据文件 ,.myi用来存放索引文件。
对于myisam存储引擎表,mysql数据库只缓存其索引文件, 数据文件的缓存由操作系统本身来完成。
InnoDB
支持事务,主要面向在线事务处理(OLTP)方面的应用。
行锁设计,支持外键,即默认情况下读取操作不加锁。
InnoDB是为处理巨大数据量时的最大性能设计。
附注:
行锁:写入、更新操作的时候将这一行锁起来, 不让其他人再操作了。
表锁:写入、更新操作时,将表给锁起来不让其他人再操作了 。
事务:同时操作多个数据,若其中的一个数据操作失败。 可回滚到操作之前。常用于银行、电商、金融等系统中
索引
MySQL的索引类型
索引类型 | 功能说明 |
普通索引 | 最基本的索引,它没有任何限制 |
唯一索引 | 某一行企用了唯一索引则不准许这一列的行数据中有重复的值。针对这一列的每一行数据都要求是唯一的 |
主键索引 | 它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引,常用于用户ID。类似于书中的页码 |
全文索引 | 对于需要全局搜索的数据,进行全文索引 |
1.普通索引
基本语法 alter table 表 add index(字段)
示例
# 示例解释 为money表的username字段增加索引
ALTER TABLE money ADD INDEX( username);
2.唯一索引
基本语法 alter table 表 add UNIQUE(字段)
示例
- 为money表的email字段增加唯一索引
ALTER TABLE money ADD UNIQUE(email);
3.全文索引
基本语法 alter table 表 add FULLTEXT(字段);
示例
# 为money表的content字段增加全文索引
ALTER TABLE money ADD FULLTEXT(content );
4.主键索引
基本语法 alter table 表 add PRIMARY KEY(字段);
示例
# 为money表的id字段增加主键索引
ALTER TABLE money ADD PRIMARY KEY(id );
5.创建表时也可以声明索引
创建表时可在创建表语句后加上对应的类型即可声明索引:
PRIMARY KEY(字段)
INDEX [索引名] (字段)
FULLTEXT [索引名] (字段)
UNIQUE[索引名] (字段)
注:中括号中的索引名,代表可选
整体示例如下:
CREATE TABLE test (
id INT NOT NULL ,
username VARCHAR(20) NOT NULL ,
password INT NOT NULL ,
content INT NOT NULL ,
PRIMARY KEY ( id ),
INDEX pw ( password),
UNIQUE ( username ),
FULLTEXT ( content )
) ENGINE = InnoDB;