MySQL数据库存储引擎及字段详细介绍

鲜鸡屎
162 阅读6分钟

字符编码与配置文件

\s  # 查看数据库基本信息 (用户，编码，版本，端口...)

my-default.ini  # windows下MySQL默认的配置文件
拷贝上述文件并且重命名为my.ini(不能是其他名字)

由于5.6版本的编码不统一，会造成乱码，我们需要统一将编码改为utf8或utf8mb4
添加字符编码相关的配置（百度查看即可）
[mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
[client]
    default-character-set=utf8
[mysql]
    default-character-set=utf8  
注意如果配置文件中涉及到了mysqld相关的配置，那么需要重启服务端才会生效(每次启动服务端都会重新加载my.ini中的配置信息)
ps：偷懒操作  在配置文件中的mysqld下提前写好用户名和密码，之后直接输入mysql即可登录(不用输入账号密码)

存储引擎

我们可以简单理解为存储引擎就是针对相同的数据采用不同的村去策略
show engines;  # 查看所有的存储引擎
#  需要掌握的存储引擎
1.MyISAM
    MySQL5.5以及之前版本的默认存储引擎
    存取数据的速度快，但是功能较少，安全性较低
2.InnoDB
    MySQL5.6之后版本默认的存取引擎
    存取数据的速度没有MyISAM快，但是支持事务、行锁、外键等诸多功能，安全性较高。
3.Memory
    基于内存的存储引擎，存取数据速度极快，但是断电立刻丢失
4.BlankHole
    黑洞  任何写进去的数据都会立刻丢失 类似于垃圾站

# 不同存储引擎之间底层文件的区别
首先在数据库db1下创建四种不同搜索引擎的表：
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
去 data\db1 文件夹下查看都有哪些文件
InnoDB
    .frm	表结构
    .ibd	表数据、表索引(加快数据查询)
MyISAM
    .frm	表结构
    .MYD	表数据
    .MYI	表索引(索引和数据文件分隔开，所以查询速度要比InnoDB快)
Memory
    .frm	表结构(随用随扔，所以没有数据文件)
BlankHole
    .frm	表结构(垃圾桶，数据放进去就丢失，同样没有数据文件)
# 验证：    
insert into t1 values(1);
select结果：
+------+
| id   |
+------+
|    1 |
+------+

insert into t2 values(2);
select结果：
+------+
| id   |
+------+
|    2 |
+------+

insert into t3 values(3);
select结果：
+------+
| id   |
+------+
|    3 |
+------+
重启服务后的select结果：
Empty set (0.00 sec)  # 数据只存在于内存中，重启立刻丢失 

insert into t4 values(4);
select结果：
Empty set (0.00 sec)  # 数据直接丢失

创建表的完整语法

create table 表名(
    字段名1 字段类型(数字) 约束条件1 约束条件2,
    字段名2 字段类型(数字) 约束条件,
    字段名3 字段类型(数字) 约束条件
)engine=存储引擎;
1.字段名和字段类型是每个字段都必须的，且建表时至少要有一个字段。
2.数字跟约束条件是可选的。
3.约束条件可以写多个 之间空格隔开。
4.最后一个字段的末尾千万不能加逗号。

实例：
mysql> create table t5(
-> id int(20) ,
-> name char(32),
-> age int(20)
-> );
Query OK, 0 rows affected (0.01 sec)

字段类型之整型

主要有以下几种整型
tinyint			1bytes	 -128-127(有负号)	0-255(没有负号)
smallint		2bytes
int			    4bytes
bigint			8bytes

1.验证整型是否有负号
    create table t6(id tinyint);
    insert into t6 values(-129),(-120),(250),(256);
    ps:发现自动填写为两个边界值，虽然能存入但是数据失真，没有意义
2.自定义移除负号
    '''unsigned 约束条件：指不需要负号'''
    create table t7(id tinyint unsigned);
    insert into t7 values(-129),(256);

'''
插入的数据值超出了字段类型的范围 不应该让其插入并自动修改 没有意义
数据库应该直接报错(这个特性需要我们手动去配置文件里边修改)
方式1：命令临时修改
    set session sql_mode='strict_trans_tables'  当前客户端操作有效
    set global sql_mode='STRICT_TRANS_TABLES'   服务端不重启永久有效
方式2：配置文件修改，永久有效
    [mysqld]
    sql_mode='STRICT_TRANS_TABLES'
'''

字段类型之浮点型

float	double	decimal
三者都可以存储浮点型数据，但是各自的精度不一样
使用方式：
    float(255,30)  # 第一个数表示总共多少位，第二个数表示小数占多少位
    double(255,30)	decimal(65,30)
验证精确度问题：
    create table t8(id float(255,30));
    create table t9(id double(255,30));
    create table t10(id decimal(65,30));
    insert into t8 values(1.11111111111111111111111111111);
    insert into t9 values(1.11111111111111111111111111111);
    insert into t10 values(1.11111111111111111111111111111);

mysql> select * from t8;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+

mysql> select * from t9;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+

mysql> select * from t10;
+----------------------------------+
| id                               |
+----------------------------------+
| 1.111111111111111111111111111110 |
+----------------------------------+

可以看到：float的精度最低，大概只有小数点后7位左右，double次之，decimal的精度最高。
float < double < decimal
'一般情况下，float的精度已经足够我们使用，如果追求完美精度，可以使用字符串代替'

字段类型之字符类型

char		定长
    char(4)
    最大只能存储四个字符，如果超过范围直接报错，如果不超过，则用空格填充至四个字符
varchar		变长
    varchar(4)
    最大只能存储四个字符，超出范围直接报错。如果不超出，则有几位就存几位数。

两者的区别：
    create table t11(id int, name char(4));
    create table t12(id int, name varchar(4));
1.结果验证 超出范围两者都会报错
    注意sql_mode='strict_trans_tables'
2.验证定长和变长特性
    char_length()  # 统计字段数据的长度
"
默认情况下char在存储数据的时候针对没有满足固定位数的字符会自动填充空格，然后再读取的时候又会自动将填充的空格移除，如果想取消该机制
sql_mode
    set global
sql_mode='strict_trans_tables,pad_char_to_full_length';
上述目录是替换，不是新增，所以之前的配置也需要写上
"
3.char VS varchar
    char 
            整存整取 速度快
            浪费存储空间
    varchar
            节省存储空间
            存取数据时慢于char(需要先判断字符的长度，类似于黏包现象)
两者的使用频率都很高  现在默认很多时候是varchar

字段类型后括号内数字的含义

数字大部分情况下都是用来限制字段的存储长度，但是整型除外！！！
整型中括号内的数字不是用来限制存储的长度，而是展示的长度
验证：
create table t13(id int(3)); 
create table t14(id int(3) zerofill);
'zerofill：约束条件：不足的部分用0补上'
insert into t13 values (12);
insert into t14 values (12);

mysql> select  * from t13;
+------+
| id   |
+------+
|   12 |
+------+

mysql> select  * from t14;
+------+
| id   |
+------+
|  012 |
+------+
总结：以后只要涉及到整型字段的定义，类型后面都不需要加括号和数字，除非业务需求必须固定位数。

字段类型之枚举与集合

枚举：关键字：enum
    多选一		eg：性别(男，女)
create table t15(
    id int,
    name varchar(32),
    gender enum('male','female','others')
);

集合： 关键字：set
    多选多		eg：爱好(唱、跳、rap、篮球)
create table t16(
    id int,
    name varchar(32),
    hobbies set('read','run','music','rap')
);

字段类型之日期类型

date		年月日
time		时分秒
datetime	年月日时分秒
year		年份

create table t17(
    id int,
    name varchar(32),
    birth date,
    reg_time datetime,
    study_time time,
    join_time year
);

mysql> desc t17;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| namr       | varchar(32) | YES  |     | NULL    |       |
| birth      | date        | YES  |     | NULL    |       |
| reg_time   | datetime    | YES  |     | NULL    |       |
| study_time | time        | YES  |     | NULL    |       |
| join_time  | year(4)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

mysql> insert into t17 values(1,'jason','2002-02-04',
    -> '2002-02-04 12:12:12','12:12:12','2002');
    
mysql> select * from t17;
+------+-------+------------+---------------------+------------+-----------+
| id   | namr  | birth      | reg_time            | study_time | join_time |
+------+-------+------------+---------------------+------------+-----------+
|    1 | jason | 2002-02-04 | 2002-02-04 12:12:12 | 12:12:12   |      2002 |
+------+-------+------------+---------------------+------------+-----------+

字段约束条件

补充知识：插入数据的方式不仅有按照创建表的顺序依次插入，还可以按照自定义顺序插入
'
insert into 表名 values(字段1,字段2)  # 默认按照创建表的字段顺序添加
insert into 表名(字段2 字段1) values(值2,值1)  # 自定义顺序
'

1.unsigned	无负号
    id int unidigned
2.zerofill	用零填充
    id int zerofill
3.not null	非空
    name varchar(32) not null
4.default	默认值
    name varchar(32) default 'jason'
5.unique	唯一值
    id int unique	单列唯一

    host varchar(32)
    port int
    unique(host,port) 联合唯一

mysql> create table ip(
-> id int unsigned not null,
-> name varchar(32) default 'jason',
-> host varchar(32),
-> port int ,
-> unique(host,port)
-> );
insert into ip values(1,'','127.0.0.1',8080);
insert into ip values(1,'','127.0.0.1',8081);
insert into ip(id,host,port) values(2,'127.0.0.1',8082);


mysql> select * from ip;
+----+-------+-----------+------+
| id | name  | host      | port |
+----+-------+-----------+------+
|  1 |       | 127.0.0.1 | 8080 |
|  2 |       | 127.0.0.1 | 8081 |
|  2 | jason | 127.0.0.1 | 8082 |
+----+-------+-----------+------+

mysql> desc ip;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | NULL    |       |
| name  | varchar(32)      | YES  |     | jason   |       |
| host  | varchar(32)      | YES  | MUL | NULL    |       |
| port  | int(11)          | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
avatar
文章
阅读
粉丝