MySQL创建用户，B+树，触发器以及存储过程

关于MySQL严格模式下用插入表单的形式下创建用户，B+树中page的大小能否进行修改以及了解MySQL的触发器以及存储过程

插入表单的形式下创建用户

先提条件，打开MySQL的严格模式；（本人不确定是否默认开启，所以找了命令开启一下哎）；

set sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

开启之后进入用root账户登入MySQL；

先去尝试用插表的形式去创建；

insert into user(host,user,password,select_priv,insert_priv,updat_priv) values (‘localhost’,‘guest’,password(‘guest123’),‘y’,‘y’,‘y’);

发现会报错；（语法错误）
在菜鸟教程中有个注意：
在 MySQL5.7 中 user 表的 password 已换成了authentication_string。
我们去查看user表中的password是否换成了authentication_string；

 select host,user,authentication_string from user;

很明显是的，但是我们命令中有两个password第一个是表示字段，那第二个呢？

我们把两个都换成authentication_string试一试：

mysql> insert into user (host,user,authentication_string,select_priv,insert_priv,update_priv) values(‘localhost’,‘guest’,authentication_string(‘guest123’),‘Y’,‘Y’,‘Y’);

还是会报错，但是菜鸟教程里还有一个注意：
password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
所以我们把第一个字段改成authentication_string，第二个函数改成MD5（）；

insert into user(host,user,authentication_string,select_priv,insert_priv,update_priv) values ('localhost','guest',md5(123456),'y','y','y');

它报错了，但是多的是关于ssl_cipher的一个错误，我去找了百度翻译：

他说是没有给这个字段赋值；所以我们用管理员的ssl_cipher字段值来当我们的参考，

select ssl_cipher from user ;

空的？ 我不确定就在结尾加了\G试试

好的，那我们就把这个字段加在命令里运行试试看；

insert into user(host,user,authentication_string,select_priv,insert_priv,update_priv,ssl_cipher) values ('localhost','guest',md5(123456),'y','y','y',' ');

出错了 但明显跟之前的一样 没有赋值；索性我就直接给他加了个空格；

 insert into user(host,user,authentication_string,select_priv,insert_priv,update_priv,ssl_cipher,x509_issuer) values ('localhost','guest',md5(123456),'y','y','y',' ',' ');

又是一个 没关系 加上！

insert into user(host,user,authentication_string,select_priv,insert_priv,update_priv,ssl_cipher,x509_issuer,x509_subject) values ('localhost','guest',md5(123456),'y','y','y',' ',' ',' ');

它成功了~
我赶紧刷新了一下就去查看：

select user from user;

是的我们在user表单下创建了一个名为guest的用户
我就退出去登录一下这个用户：

他甚至登陆不上，也就意味着我创建一个不能用的用户
所以要创建用户的话还是老老实实用现在的create命令来创建吧

命令和过程我是看别人的文章来做的可以去看看http://t.csdn.cn/Jy1bw

B+树

B+树是B树的一个变种，它把所有的关联数据都存储在叶子结点中，内部结点只存放关键字和孩子指针，因此最大化了内部结点的分支因子。使其更适合实现外存储索引结构。

由于B树中每一个节点占用一个磁盘块的空间。而操作系统从磁盘读取数据到内存中是以磁盘块为单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么读取什么。

InnoDB存储引擎中有页（page）的概念，页是其磁盘管理的最小单位，在InnoDB存储引擎中默认每个页的大小为16K，B+树将每个节点的大小设置和页大小一样，都为16K，因此读取一个节点，只需要一次IO。

而B树中每个节点中不仅包含数据的key值，还会包含除了key值外的所有data值。而每一页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度很大，增大查询时的磁盘IO次数，进而影响查询效率。而B+树中，所有数据节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息， 这样可以大大加大每个节点存储的key值数量， 降低B+树的高度，减少查询时的IO次数。

InnoDb表的存储引擎中一页的大小为16k，一般表的主键类型占用8个字节，指针占用8个字节，那么一页可以存放16kb/(8b*8b)=1000个键值（关键字），所以一个高度为3的B+树可以存储1000^3即10亿条记录。
至于改变页的大小个人不能随意猜测，从某种意义上来说改变大小确实能存放更多的数据，但是究竟该多少才合理也确实是个不小的问题。

触发器以及存储过程

在MySQL中，触发器是一组SQL语句，当对关联表上的数据进行更改时会自动调用这些语句。可以定义触发器在INSERT，UPDATE或DELETE语句更改数据之前或之后调用。在MySQL 5.7.2版之前，您可以为每个表定义最多六个触发器。

BEFORE INSERT - 在将数据插入表格之前激活。
AFTER INSERT - 将数据插入表格后激活。
BEFORE UPDATE - 在更新表中的数据之前激活。
AFTER UPDATE - 更新表中的数据后激活。
BEFORE DELETE - 在从表中删除数据之前激活。
AFTER DELETE - 从表中删除数据后激活。

但是，从MySQL版本5.7.2+开始，您可以为同一触发事件和操作时间定义多个触发器。

当您使用不使用语句INSERT，DELETE或UPDATE语句来修改表中的数据，与表关联的触发器不被调用。例如，TRUNCATE语句删除表的所有数据，但不调用与表关联的触发器。

有一些语句使用INSERT其它语句，如REPLACE语句 或 LOAD DATA语句。如果使用这些语句，则会调用与表关联的相应触发器。

必须为与表关联的每个触发器使用唯一名称。但是，您可以为不同的表定义相同的触发器名称，但这是一种很好的做法。

您应使用以下命名约定命名触发器：

(BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE) 
例如，before_order_update是在order更新表中的行之前调用的触发器。

以下命名约定与上面的命名约定一样好。

tablename_(BEFORE | AFTER)_(INSERT| UPDATE | DELETE) 
例如，order_before_update与before_order_update上面的触发器相同。

MySQL触发存储
MySQL将触发器存储在数据目录中，例如，/data/mysqldemo/使用名为的文件tablename.TRG和 triggername.TRN：

tablename.TRG文件将触发器映射到相应的表。
triggername.TRN文件包含触发器定义。
您可以通过将触发器文件复制到备份文件夹来备份MySQL触发器。您还可以使用mysqldump工具备份触发器。

MySQL触发器限制
MySQL触发器涵盖标准SQL中定义的所有功能。但是，在应用程序中使用它们之前，您应了解一些限制。

MySQL触发器不能：

使用SHOW，LOAD DATA，LOAD TABLE，BACKUP DATABASE，RESTORE，FLUSH和RETURN语句。
使用隐式或显式提交或回滚的语句，例如COMMIT，ROLLBACK，START TRANSACTION，LOCK / UNLOCK 			TABLES，ALTER，CREATE，DROP，   RENAME。
使用 准备语句 PREPARE和EXECUTE之类的
使用动态SQL语句。

存储过程的定义
存储过程是存储在数据库目录中的一段声明性SQL语句。存储过程可以由触发器，其他存储过程以及Java， Python，PHP等应用程序调用。

mysql存储过程

调用自身的存储过程称为递归存储过程。大多数数据库管理系统都支持递归存储过程。但是，MySQL并不支持它。在MySQL中实现递归存储过程之前，您应检查MySQL数据库的版本。

MySQL中的存储过程
MySQL被称为最受欢迎的开源RDBMS，它被社区和企业广泛使用。但是，在它存在的第一个十年中，它不支持存储过程，存储函数，触发器和事件。从MySQL 5.0开始，这些功能被添加到MySQL数据库引擎中，使其更加灵活和强大。

MySQL存储过程的优点

通常存储过程有助于提高应用程序的性能。一旦创建，存储过程就会被编译并存储在数据库中。但是，MySQL实现的存储过程略有不同。MySQL存储过程是按需编译的。编译存储过程后，MySQL将其放入缓存并为每个连接维护自己的存储过程缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则，存储过程的工作方式类似于查询。
存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序必须只发送存储过程的名称和参数，而不是发送多个冗长的SQL语句。
存储过程对任何应用程序都是可重用且透明的。存储过程将数据库接口公开给所有应用程序，以便开发人员不必开发存储过程中已经支持的功能。
存储过程是安全的。数据库管理员可以为访问数据库中的存储过程的应用程序授予适当的权限，而无需为基础数据库表提供任何权限。

除了这些优点之外，存储过程也有其自身的缺点，在数据库中使用它们之前应注意这些缺点。

MySQL存储过程的缺点

如果使用许多存储过程，则使用这些存储过程的每个连接的内存使用量将显着增加。此外，如果在存储过程中过度使用大量逻辑操作，CPU使用率将会增加，因为数据库服务器没有针对逻辑操作进行良好设计。
存储过程的构造不是为开发复杂和灵活的业务逻辑而设计的。
调试存储过程很困难。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL没有提供调试存储过程的工具。
开发和维护存储过程并不容易。开发和维护存储过程通常需要一种并非所有应用程序开发人员都具备的专业技能。这可能会导致应用程序开发和维护阶段出现问题。

MySQL存储过程各有优缺点。在开发应用程序时，应根据应用程序的体系结构决定是否使用存储过程。