MySQL存储引擎分类

• MyISAM存储引擎是非事务的，锁粒度是表级的，主要应用于select，insert，不适合应用于读写操作频繁的场景，因为对于读写操作会进行锁表操作。

• MRG_MYISAM和MyISAM差不多

• Innodb存储引擎是支持事务，支持MVCC行级锁，适合任何场景

• Archive存储引擎不支持事务，支持行级锁，支持insert、select，适用于随机读取、更新、删除。

• Ndb Cluster是MySQL集群存储引擎 ，支持事务，支持行级锁，具有高可用性

数据类型

MySQL常见的数据类型有数值类型、字符串类型和日期类型，下面我们将分别详细介绍。

1. 数值类型

数值类型可以分为整数类型和实数类型。

其中，M表示整数类型的最大显示宽度； 对于浮点和定点类型，M是可以存储的总位数（精度）；对于字符串类型，M是最大长度。 M的最大允许值取决于数据类型。注意：当我们只存储两位数时，一种错误用法就是int(2)，其实int(i)存储空间是由数据类型和是否是unsigned决定的，i只是表示显示长度。int(11)和int(1)的存储空间是没差别的， int(11)的数据长度如果小于11，则默认补充空格，如果int (11)字段被zerofill修饰，则默认补充0，而且字段被zerofill修饰会自动添加unsigned。因此，当我们只存储两位数时，使用tinyint才能达到我们简约空间的目的。

注意：Decimal类型是精确类型的，如果我们需要存储精确数据，例如财务数据就必须使用Decimal类型，而不能使用float和double类型。

2. 字符串类型

字符串类型中有两种类型：char和varchar。

varchar(n)的存储规范

• varchar存储变长内容，varchar需要额外的空间记录内容长度，当内容小于255字节时，需要一个额外字节，当内容大于255字节时，需要2个额外字节；

• 在jbk编码下，char占2个字节，在UTF-8编码下，char占用3个字节；

• MySQL每行最多存储65535个字节；

• varchar中的第1个字节表示是否为空，第二个字节和第3个字节表示长度，剩下字节表示实际内容，因此最大可用存储65535-1-2=65532 ；

create table t1(c varchar(n)) charset=gbk,则此处n的最大值为(65535-1-2)/2=32766create t(c int ,c2 char(30),c3 varchar(n)) charset=utf8,则此处n的最大值（65535-1-2-4-90）/3=21812

• varchar（100）中的100指的是100个字符数量；

• 使用场景：字符串列的最大长度比平均长度大很多；字符串列很少被更新，由于varchar类型长度不固定，那么进行更新时，可能导致存储页的分裂，引起存储碎片；使用多字节字符集存储字符串，以UTF-8为例，存储中文需要3个字节，而存储英文或数字只需要1个字节。

char(n)的存储规范

• char类型是定长的；

• 字符串存储在char类型的列中会删除末尾的空格（见下图）；

• char类型的最大宽度是255字节。

• 适用场景： char类型适合存储长度近似的值，例如身份证、md5值，手机号等；char类型适合存储短字符串，例如性别字段，

使用char(1)就比varchar(1)更节省空间，因为varchar还需要存储额外字节存储其他信息；char类型适合存储经常更新的字符序列，由于char类型的长度是固定的，MySQL会一次性地分配存储空间，在多次更新时也不会出现页分裂的情况，减少存储碎片。

3. 日期类型

注意：timestamp占4byte，实际上是用int存储的。由于只有4byte，因此它只能显示1970-01-01 到 2038-01-19，也正是这个原因，如果在这个时间内的字段推荐使用timestamp。timestamp类型显示依赖于所指定的时区。除此之外，还需要注意一点，在行的数据修改时可以自动修改timestamp列的值，这个特性可以帮助我们在进行数据分析时自动提取出最新变化的数据。

MySQL5.5 datetime类型字段不能设置默认值为now()

MySQL5.6 datetime类型字段可以设置默认值为now()

• from_unixtime():数字转换成时间

• unix_timestamp():时间转换成数字

• Date_sub/DATE_ADD:对时间进行加减

• CURDATE():将当前日期按照'YYYY-MM-DD'

• NOW():返回当前日期和时间值

常见操作

1. DML（Data manipulation language，数据操作语言）

DML分成数据查询和数据更新两类，数据更新又分成插入、删除和修改三种操作，主要命令：select、insert、delete、update、merge、call、explain、plan

2. DDL（Data definition language，数据定义语言）

DDL包括两种类型数据：数据字典以及数据类型和结构定义。主要命令：create 、alter、drop、truncate、rename、comment

create用于创建语句，用于创建表或者数据库或者存储过程或者其他内容

create table tea(id int not null auto_increment,name varchar(6) not null,remark char(6),primary key(id));show create table tea;

alter一般用于添加或者修改表中的字段名或者字段定义。也可以用于修改字段顺序等。同样的也可以用于修改数据库的名字或者编码

修改字段定义:ALTER TABLE emp MODIFY first_name VARCHAR(20) NOT NULL DEFAULT '-'; 添加字段:alter table emp add column age int(3) not null default 0; 修改字段名字，同时修改定义:alter table emp change age age1 int(4) not null default 0; 在表Column的尾部追加新的column:alter table emp add birth date not null after empno; 移动column到表column的首位:alter table emp modify age1 int(3) not null default 0 first; 将某个column移动到另一个columon后面:alter table department change deptno deptno int(11) NOT NULL after deptname; 删除主键:alter visit_log drop primary key; 修改表名:alter table emp rename employees;删除字段alter table guess_product_info drop column backstyle;

3. DCL（Data Control Language，数据控制语言）

赋予权限grant all privileges on *.* to 'root'@'localhost' with grant option; 授权远程用户注意不要with grant optiongrant select,update,create,delete on *.* to guest@'115.220.46.%' identified by 'test@home';创建业务用户，并要求只限制IP段查看权限Select user,password,host from mysql.user;Show grants for root@'localhost';select user,password,host ,grant_priv,super_priv from mysql.user;重命名用户rename mysql.user root@' ' to admin@' ';刷新权限flush privileges回收权限revoke select,insesrt,update,delete,create on *.* from 'guest'@'115.220.46.%';show grants for guest@'115.220.46.%';修改密码update mysql.user set password =password('test') where user='guest';password()函数对字符串进行MD5加密mysqladmin -uroot -ptest password 12345 -p 旧密码 password 新密码忘记密码mysqld_safe --defaults-file=/etc/mysql/my3306.cnf --skip-grant-tables &删除用户drop user root@'';删除test数据库select * from mysql.db where db like '%test%'\G;delete from mysql.db where db like '%test%';删除不需要的用户mysql> select user,password,host from mysql.user;+------+-------------------------------------------+------------+| user | password | host |+------+-------------------------------------------+------------+| root | | localhost || root | | nbview.com || root | | 127.0.0.1 || root | | ::1 || | | localhost || | | nbview.com || kewy | kewy126@home | % || root | *08F411191A8F7130F09F0A961DB8E87983620D5B | % |+------+-------------------------------------------+------------+8 rows in set (0.00 sec)mysql> delete from mysql.user where user='kewy';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 59Current database: student_db1Query OK, 1 row affected (0.01 sec)mysql> delete from mysql.user where user='root' and host='::1';Query OK, 1 row affected (0.00 sec)mysql> delete from mysql.user where user='root' and host='nbview.com';Query OK, 1 row affected (0.00 sec)mysql> delete from mysql.user where host='nbview.com';Query OK, 1 row affected (0.00 sec)mysql> delete from mysql.user where host='localhost' and user is null;Query OK, 0 rows affected (0.00 sec)mysql> delete from mysql.user where host='127.0.0.1' ;Query OK, 1 row affected (0.00 sec)mysql> select user,password,host from mysql.user;+------+-------------------------------------------+-----------+| user | password | host |+------+-------------------------------------------+-----------+| root | | localhost || root | *08F411191A8F7130F09F0A961DB8E87983620D5B | % |+------+-------------------------------------------+-----------+2 rows in set (0.00 sec)

4. TCL（Transaction Control Language，事务控制语言）

• TCL用于控制事务内执行流程的语言

• start transaction|Begin:开始一个事务

• Savepoint：创建一个记录点，方便回滚到这个地方

• Rollback:回滚事务

• Commit：提交事务

开始事务START TRANSACTION 可选:创建一个存档点SAVEPOINT sp 开始操作SQLselect insert ...操作完成提交数据COMMIT 或者，操作失败，回滚数据(回滚到某个存档点)ROLLBACK ROLLBACK TO sp 释放存档点RELEASE SAVEPOINT sp 

---

分享到:

---

閱讀更多 末路狂奔在路上 的文章

關鍵字: 存储 引擎 读写操作

---