MySQL 51简体中文手册 第16章：编写自定义存储引擎

深⼊mysql创建⾃定义函数与存储过程的详解在使⽤mysql的过程中，mysql⾃带的函数可能不能完成我们的业务需求，这时就需要⾃定义函数,例如笔者在开发过程中遇到下⾯这个问题：复制代码代码如下:DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`pic` varchar(50) NOT NULL,`hashcode` varchar(16) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of test-- ----------------------------INSERT INTO `test` VALUES ('1', '2012120910403250c3fa209bf48.jpg', 'bf8f83818080c0f1');INSERT INTO `test` VALUES ('2', '2012120620430750c092db26557.JPG', 'ff9880f0f680ceff');INSERT INTO `test` VALUES ('3', '2012120619582550c08861eb062.jpg', '7f7f004f7f7f7c7f');INSERT INTO `test` VALUES ('4', '2012112911072650b6d16e7f21f.jpg', '7f7f004f7f7f007f');其中pic字段为图⽚名称，hashcode是图⽚的感知哈希编码（16进制编码字符串，长度固定16位），⽤户输⼊⼀个hashcode，怎么从数据库中找出满⾜字符串对应位置的字符不同的个数⼩于5的记录呢?就像“11001”和“11101”对应位置不同字符不同的个数为1，⽐如⽤户输⼊"7f7f004f7f7f00af",那么第三条和第四条记录是满⾜的，怎么实现呢？如果单纯的依靠mysql⾃带的函数很难完成，这时就需要建⽴⾃定义函数解决。

如何在MySQL中使用存储引擎引言：MySQL是一个开源的关系型数据库管理系统，广泛应用于各类应用程序中。

在MySQL中，存储引擎是对数据的存储和访问进行管理的组件。

MySQL提供了多种存储引擎供用户选择和使用，每种存储引擎都有其特定的优势和适用场景。

本文将介绍MySQL中常见的存储引擎及其使用方法，以帮助读者更好地理解和应用MySQL中的存储引擎。

一、存储引擎的概念存储引擎是指负责管理数据存储和访问的软件组件，它可以是MySQL默认的InnoDB引擎，也可以是其他第三方开发的引擎，如MyISAM、Memory等。

存储引擎的选择将直接影响到数据库的性能、事务支持、并发控制等方面。

二、常见的存储引擎1. InnoDB引擎InnoDB是MySQL默认的事务型存储引擎，它提供了高并发的事务处理、ACID事务支持、行级锁定等功能。

对于需要频繁进行读写操作和事务处理的应用，使用InnoDB是一个较好的选择。

2. MyISAM引擎MyISAM是MySQL默认的非事务型存储引擎，它提供了快速的插入和查询操作，适用于读多写少的应用场景。

由于不支持事务和行级锁定，MyISAM引擎在并发处理和数据完整性方面相对弱一些。

3. Memory引擎Memory引擎也称为Heap引擎，它将数据存储在内存中，提供了非常快速的数据读写操作。

Memory引擎适用于需要高速读写操作，但对数据持久性要求不高的应用场景。

由于数据存储在内存中，MySQL重启或崩溃时数据将丢失。

4. Archive引擎Archive引擎主要用于存档和长期保存数据，它采用高度压缩的数据存储方式，能够显著减少存储空间的占用。

Archive引擎适用于大量历史数据的存储，对于查询和更新操作的效率相对较低。

5. NDB Cluster引擎NDB Cluster引擎是MySQL集群的核心存储引擎，它提供了高性能的分布式数据库存储和处理能力。

NDB Cluster引擎适用于高可用性和高并发的应用场景，可以实现数据在不同节点间的自动分布和复制。

初识庐山真面目——MySQL数据集简答题1. MySQL数据库可以称得上是目前运行速度最快的SQL语言数据库。

除了具有许多其他数据库所不具备的功能和选择之外，MySQL数据库还是一种完全免费的产品，用户可以直接从网上下载使用，而不必支付任何费用。

MySQL数据库的跨平台性是一个很大的优势。

2. MySQL名称的起源不明。

10多年来，我们的基本目录以及大量库和工具均采用了前缀“my”。

不过，共同创办人Monty Widenius的女儿名字也叫“My”。

时至今日，MySQL 名称的起源仍是一个迷，即使对我们也一样。

MySQL Dolphin（我方徽标）的名称为“Sakila”，它是由MySQL AB公司的创办人从用户在“Dolphin命名”比赛中提供的众多建议中选定的。

该名称是由来自非洲斯威士兰的开放源码软件开发人Ambrose Twebaze提出的。

根据Ambrose的说法，按斯威士兰的本地语言，女性化名称Sakila源自SiSwati。

Sakila也是坦桑尼亚、Arusha地区的一个镇的镇名，靠近Ambrose的母国乌干达。

MySQL从无到有，到技术的不断更新，版本的不断升级，经历了一个漫长的过程，这个过程是实践的过程，是MySQL成长的过程。

时至今日，MySQL的版本已经更新到了mysql-5.6。

下面这个MySQL官方网站上的截图，足可以反应出MySQL的成长历程。

3. MySQL是一个真正的多用户、多线程SQL数据库服务器。

SQL（结构化查询语言）是世界上最流行的和标准化的数据库语言。

下面看一下MySQL的特性：❑使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性。

❑支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统。

❑为多种编程语言提供了API。

MySQL⾃定义函数和存储过程⽰例详解前⾔本⽂主要给⼤家介绍的是关于MySQL⾃定义函数和存储过程的相关内容，分享出来供⼤家参考学习，下⾯话不多说了，来⼀起看看详细的介绍吧1、前置条件MySQL数据库中存在表user_info，其结构和数据如下：mysql> desc user_info;+-----------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+----------+------+-----+---------+-------+| id | int(10) | NO | PRI | NULL | || name | char(20) | NO | | NULL | || passwd | char(40) | NO | | NULL | || email | char(20) | NO | | NULL | || phone | char(20) | NO | | NULL | || role | char(10) | NO | | NULL | || sex | char(10) | NO | | NULL | || status | int(10) | NO | | NULL | || createAt | datetime | NO | | NULL | || exprAt | datetime | NO | | NULL | || validDays | int(10) | NO | | NULL | || delAt | datetime | YES | | NULL | |+-----------+----------+------+-----+---------+-------+12 rows in set (0.10 sec)mysql> select * from user_info;+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+| id | name | passwd | email | phone | role | sex | status | createAt | exprAt | validDays | delAt |+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+| 1 | StephenWang7 | py123456 | 123@ | 151******** | admin | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL || 2 | StephenWang8 | 123456 | 123@ | 151******** | viewer | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL |+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+2 rows in set (0.00 sec)2、⾃定义函数函数：可以完成特定功能的⼀段SQL集合。

MySQL中⽂参考⼿册1 MySQL 的⼀般信息这是MySQL参考⼿册；它记载了MySQL版本3.23.7-alpha。

MySQL 是⼀个快速、多线程、多⽤户和强壮的SQL数据库服务器。

对Unix和 OS/2 平台，MySQL基本上是免费的；但对微软平台，你在30 天的试⽤期后必须获得⼀个MySQL 许可证。

详见。

提供有关MySQL的最新信息。

对于MySQL能⼒的讨论，详见。

对于安装指南，见。

对于有关移植MySQL到新机器或操作系统的技巧，参见。

有关从 3.21 版升级的信息，详见。

MySQL的⼊门教程，见。

SQL和基准信息的例⼦，见基准⽬录(在分发中的'sql-bench'⽬录)。

对于新特征和错误修复⼀个历史记录，见。

对于当前已知错误和功能缺陷的⼀张列表，见。

未来计划，见。

这个计划的所有贡献者的名单，见。

重要：将臭⾍(错误)报告、问提和建议发到邮件列表（原⽂未提供）。

对源代码分发，mysqlbug 脚本可在‘scripts’⽬录下找到。

对⼆进制的分发，mysqlbug可在‘bin’⽬录下找到。

如果你有任何关于这本⼿册的增补或修正的任何建议，请将它们发给⼿册⼩组()。

1.1 什么是 MySQL？MySQL是⼀个真正的多⽤户、多线程SQL数据库服务器。

SQL（结构化查询语⾔）是世界上最流⾏的和标准化的数据库语⾔。

MySQL是以⼀个客户机/服务器结构的实现，它由⼀个服务器守护程序mysqld和很多不同的客户程序和库组成。

SQL是⼀种标准化的语⾔，它使得存储、更新和存取信息更容易。

例如，你能⽤SQL语⾔为⼀个⽹站检索产品信息及存储顾客信息，同时MySQL也⾜够快和灵活以允许你存储记录⽂件和图像。

MySQL 主要⽬标是快速、健壮和易⽤。

最初是因为我们需要这样⼀个SQL服务器，它能处理与任何可不昂贵硬件平台上提供数据库的⼚家在⼀个数量级上的⼤型数据库，但速度更快，MySQL就开发出来。

⾃1996年以来，我们⼀直都在使⽤MySQL，其环境有超过 40 个数据库，包含 10,000个表，其中500多个表超过7百万⾏，这⼤约有100 个吉字节(GB)的关键应⽤数据。

MySQL存储引擎特性和线程模型InnoDB 存储引擎核⼼特性MVCC ：多版本并发控制聚簇索引：⽤来组织存储数据和优化查询⽀持事务：数据安全保证⽀持⾏级锁：控制并发外键多缓冲区⽀持⾃适应Hash索引： AHI复制中⽀持⾼级特性。

备份恢复：⽀持热备。

⾃动故障恢复：CR Crash Recovery双写机制： Double Write存储引擎管理查询⽀持的、默认使⽤的存储引擎mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.00 sec)mysql> select @@default_storage_engine;+--------------------------+| @@default_storage_engine |+--------------------------+| InnoDB |+--------------------------+1 row in set (0.00 sec)查询某表的存储引擎mysql> show create table test\G;*************************** 1. row ***************************Table: testCreate Table: CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`a` int(11) NOT NULL,`b` int(11) DEFAULT NULL,`c` char(10) NOT NULL,`d` varchar(10) DEFAULT NULL,`e` varchar(10) NOT NULL,PRIMARY KEY (`id`),KEY `idx` (`a`,`b`,`c`,`d`,`e`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)查看所有业务表的存储引擎信息mysql> select table_name,table_schema,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');+----------------------+--------------+--------+| TABLE_NAME | TABLE_SCHEMA | ENGINE |+----------------------+--------------+--------+| current_dept_emp | employees | NULL || departments | employees | InnoDB || dept_emp | employees | InnoDB || dept_emp_latest_date | employees | NULL || dept_manager | employees | InnoDB || employees | employees | InnoDB || salaries | employees | InnoDB || t2 | employees | InnoDB || titles | employees | InnoDB || student | school | InnoDB || t100w | test | InnoDB || test | test | InnoDB || city | world | InnoDB || country | world | InnoDB || countryLanguage | world | InnoDB || cours | world | InnoDB || order_info | world | InnoDB || score | world | InnoDB || student | world | InnoDB || t1 | world | InnoDB || t3 | world | InnoDB || t4 | world | InnoDB || t4_log | world | InnoDB || test_json | world | InnoDB || vscc | world | NULL |+----------------------+--------------+--------+25 rows in set (0.00 sec)建表时设置存储引擎mysql> create table xxx (id int) engine=innodb charset=utf8mb4;修改已有表的存储引擎mysql> alter table xxx engine=myisam;mysql> alter table world.xxx engine=innodb;将所有⾮InnoDB存储引擎的表修改为InnoDB1、查出所有⾮InnoDB引擎的表mysql> select table_name,table_schema,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine != 'innodb';2、备份⾮InnoDB的表mysql> select concat('mysqldump -uroot -p123456 ',table_schema,'',table_name,' > /backup/',table_schema,'_',table_name,'_',curdate(),'.sql') from information_schema.tables where table_schema not in ('sys','mysql','information_schema3、修改存储引擎mysql> select concat("alter table ",table_schema,".",table_name,"engine=innodb;") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb';修复表碎⽚alter table world.xxx engine=innodb;analyze table world.city;InnoDB体系结构---线程详解线程结构-Master ThreadMySQL的master线程在后台执⾏多种I/O相关的任务。

以下文章收集的主题包括：MyISAM和Innodb（及其他）存储引擎的特点、优势、缺点，技术细节差异，这两者的性能表现的比较，各自适合应用于怎样的业务和项目。

Innodb对事务控制的支持（原理），行级锁，具体操作。

检查修复工具myisamchk和mysqlcheck对MyISAM的支持（和对Innodb的部分支持），MyISAM的备份与恢复技巧。

Innodb数据文件ibdata*和日志文件ib_logfile*。

Innodb的Per-Table表空间。

MySQL数据表在系统中表现形式有：ISAM、MyISAM、MERGE、BDB、InnoDB和HEAP。

每种数据表在文件系统中都有不同的表示方式，有一个共同点就是每种数据表至少有一个存放数据表结构定义的.frm文件。

ISAM数据表是最原始的数据表（.frm,.ISD,.ISM），MyISAM数据表是ISAM数据表的继承者。

InnoDB 由于采用表空间的概念来管理数据表，所以它只有一个与数据表对应.frm文件，同一目录下的其它文件表示为表空间，存储数据表的数据和索引。

数据目录是用来存放数据表和相关信息的地方，是数据库的核心。

在WINDOWS系统上，数据目录的位置默认是在c:/mysql/data中。

数据目录除存放数据库文件外，还会存放日志文件，状态文件，配置文件，DES密钥文件或服务器的SSL证书与密钥文件等。

在MySQL中，每个数据库其实就是在数据目录下一个子目录，show databases命令相当于列出数据目录中的目录清单。

所以可以通过操作数据目录中的子目录来新建（create）、删除（drop）数据库。

但drop database db_name命令不能删除db_name目录中创建的其它非数据表文件；且由于InnoDB是表空间来管理数据表，所以不能用rm或del命令删除InnoDB的数据表。

参照：mysql学习笔记第三章：D:\superFile\Documents\A-学习编程\数据库\mysql\完整教程_mysql 学习笔记.docMySQL Storage Engine 小记这段时间在看《High Performance MySQL》，看到存储引擎这个地方感到很多细节比较陌生，所以总结小记一些。

第16章：编写自定义存储引擎目录16.1. 前言16.2. 概述16.3. 创建存储引擎源文件16.4. 创建handlerton16.5. 对处理程序进行实例化处理16.6. 定义表扩展16.7. 创建表16.8. 打开表16.9. 实施基本的表扫描功能16.9.1. 实施store_lock()函数16.9.2. 实施external_lock()函数16.9.3. 实施rnd_init()函数16.9.4. 实施info()函数16.9.5. 实施extra()函数16.9.6. 实施rnd_next()函数16.10. 关闭表16.11. 为存储引擎添加对INSERT的支持16.12. 为存储引擎添加对UPDATE的支持16.13. 为存储引擎添加对DELETE的支持16.14. API引用16.14.1. bas_ext16.14.2. close16.14.3. create16.14.4. delete_row16.14.5. delete_table16.14.6. external_lock16.14.7. extra16.14.8. info16.14.9. open16.14.10. rnd_init16.14.11. rnd_next16.14.12. store_lock16.14.13. update_row16.14.14. write_row16.1. 前言对于MySQL 5.1，MySQLAB公司引入了插件式存储引擎体系结构，这样，就能创建新的存储引擎，并将它们添加到正在运行的MySQL服务器上，而不必重新编译服务器本身。

该体系结构简化了新存储引擎的开发和部署。

本章的意图是作为指南，用于帮助你为新的插件式存储引擎体系结构开发存储引擎。

关于MySQL插件式存储引擎体系结构的更多信息，请参见第14章：插件式存储引擎体系结构。

16.2. 概述MySQL服务器采用了模块化风格。

MySQL之存储引擎存储引擎的概念是MySQL的一个特性，可简单理解为表类型；每一个表都有一个存储引擎，可在创建时指定，也可之后使用ALTER TABLE语句修改，都是通过ENGINE关键字设置的；若创建时没有指定，则为默认存储引擎，默认存储引擎也可通过参数文件中default-table-type参数修改。

----查看当前默认存储引擎，默认为MyISAMmysql> show variables like 'table_type';----查看当前数据库支持的存储引擎mysql> show engines \G;从输出结果可看到支持多个存储引擎，下面介绍几个重要的存储引擎如下：MyISAM默认存储引擎，不支持事务、外键，但访问速度快，对事务完整性不要求，适合于以SELECT/INSERT 为主的表；每个MyISAM物理上存储为3个文件，文件名与表名相同，扩展名分别为：.frm(存储表定义)、MYD(MYData存储数据)、MYI(MYIndex存储索引)，其中数据文件和索引文件可以放置在不同目录，平衡I/O。

数据文件和索引文件的路径，需要在创建表时通过DATA DIRECTORY和INDEX DIRECTORY语句指定，需要绝对路径，且具有访问权限。

MySQL类型的表可能因各种原因损坏，可通过CHECK TABLE语句检查表的健康，使用REPAIR TABLE 语句修改。

MySQL类型的表支持3中不同的存储格式，如下：静态表：默认存储格式，字段长度固定，存储迅速，容易缓存，缺点是占用空间多。

注意：字段存储按照宽度定义补足空格，应用访问时去掉空格，若字段本身就带有空格，也会去掉，这点特别注意。

动态表：变长字段，记录不是固定长度，优点是占用空间少，但频繁的更新删除操作会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk –r命令来改善，出现故障时难以恢复。