<p>前面我们主要分享了MySQL中的常见知识与使用。这里我们主要分享一下MySQL中的高阶使用，主要包括：函数、存储过程和存储引擎。</p>    <p>对于MySQL中的基础知识，可以参见</p>    <p><a href="/misc/goto?guid=4959749498605011161" rel="nofollow,noindex">《与 MySQL 的零距离接触》</a></p>    <h2>1 函数</h2>    <p>函数可以返回任意类型的值，也可以接收这些类型的参数。</p>    <h2>字符函数</h2>    <table>     <thead>      <tr>       <th>函数名称</th>       <th>描述</th>      </tr>     </thead>     <tbody>      <tr>       <td>CONCAT()</td>       <td>字符连接</td>      </tr>      <tr>       <td>CONCAT_WS()</td>       <td>使用指定的分隔符进行字符连接</td>      </tr>      <tr>       <td>FORMAT()</td>       <td>数字格式化</td>      </tr>      <tr>       <td>LOWER()</td>       <td>转换成小写字母</td>      </tr>      <tr>       <td>UPPER()</td>       <td>转换成大写字母</td>      </tr>      <tr>       <td>LEFT()</td>       <td>获取左侧字符</td>      </tr>      <tr>       <td>RIGHT()</td>       <td>获取右侧字符</td>      </tr>      <tr>       <td>LENGTH()</td>       <td>获取字符串长度</td>      </tr>      <tr>       <td>LTRIM()</td>       <td>删除前导空格</td>      </tr>      <tr>       <td>RTRIM()</td>       <td>删除后续空格</td>      </tr>      <tr>       <td>TRIM()</td>       <td>删除前导和后续空格</td>      </tr>      <tr>       <td>SUBSTRING()</td>       <td>字符串截取</td>      </tr>      <tr>       <td>[NOT] LIKE</td>       <td>模式匹配</td>      </tr>      <tr>       <td>REPLACE()</td>       <td>字符串替换</td>      </tr>     </tbody>    </table>    <p>函数可以嵌套使用。</p>    <p>% （百分号）：代表任意个字符。</p>    <p>_ （下划线）：代表任意一个字符。</p>    <pre>  <code class="language-sql"># 删除前导'?'符号  SELECT TRIM(LEADING '?' FROM '??MySQL???');  # 删除后续'?'符号  SELECT TRIM(TRAILING '?' FROM '??MySQL???');  # 删除前后'?'符号  SELECT TRIM(BOTH '?' FROM '??My??SQL???');  # 将'?'符号替换成'!'符号  SELECT REPLACE('??My??SQL???', '?', '!');  # 从中'MySQL'第1个开始，截取2个字符  SELECT SUBSTRING('MySQL', 1, 2);  # 从中'MySQL'截取最后1个字符  SELECT SUBSTRING('MySQL', -1);  # 从中'MySQL'第2个开始，截取至结尾  SELECT SUBSTRING('MySQL', 2);  </code></pre>    <h2>数值运算符函数</h2>    <table>     <thead>      <tr>       <th>函数名称</th>       <th>描述</th>      </tr>     </thead>     <tbody>      <tr>       <td>CEIL()</td>       <td>进一取整</td>      </tr>      <tr>       <td>DIV</td>       <td>整数除法</td>      </tr>      <tr>       <td>FLOOR()</td>       <td>舍一取整</td>      </tr>      <tr>       <td>MOD</td>       <td>取余数（取模）</td>      </tr>      <tr>       <td>POWER()</td>       <td>幂运算</td>      </tr>      <tr>       <td>ROUND()</td>       <td>四舍五入</td>      </tr>      <tr>       <td>TRUNCATE()</td>       <td>数字截取</td>      </tr>     </tbody>    </table>    <h2>比较运算符函数</h2>    <table>     <thead>      <tr>       <th>函数名称</th>       <th>描述</th>      </tr>     </thead>     <tbody>      <tr>       <td>[NOT]BETWEEN…AND..</td>       <td>[不]在范围之内</td>      </tr>      <tr>       <td>[NOT]IN()</td>       <td>[不]在列出值范围内</td>      </tr>      <tr>       <td>IS[NOT]NULL</td>       <td>[不]为空</td>      </tr>     </tbody>    </table>    <h2>日期时间函数</h2>    <table>     <thead>      <tr>       <th>函数名称</th>       <th>描述</th>      </tr>     </thead>     <tbody>      <tr>       <td>NOW()</td>       <td>当前日期和时间</td>      </tr>      <tr>       <td>CURDATE()</td>       <td>当前日期</td>      </tr>      <tr>       <td>CURTIME()</td>       <td>当前时间</td>      </tr>      <tr>       <td>DATE_ADD()</td>       <td>日期变化</td>      </tr>      <tr>       <td>DATEDIFF()</td>       <td>日期差值</td>      </tr>      <tr>       <td>DATE_FORMAT()</td>       <td>日期格式化</td>      </tr>     </tbody>    </table>    <pre>  <code class="language-sql"># 时间增加1年  SELECT DATE_ADD('2016-05-28', INTERVAL 365 DAY);  # 时间减少1年  SELECT DATE_ADD('2016-05-28', INTERVAL -365 DAY);  # 时间增加3周  SELECT DATE_ADD('2016-05-28', INTERVAL 3 WEEK);  # 日期格式化  SELECT DATE_FORMAT('2016-05-28', '%m/%d/%Y');  # 更多时间格式可以前往MySQL官网查看手册  </code></pre>    <h2>信息函数</h2>    <table>     <thead>      <tr>       <th>函数名称</th>       <th>描述</th>      </tr>     </thead>     <tbody>      <tr>       <td>CONNECTION_ID()</td>       <td>连接ID</td>      </tr>      <tr>       <td>DATEBASE()</td>       <td>当前数据库</td>      </tr>      <tr>       <td>LAST_INSERT_ID()</td>       <td>最后插入记录的ID号</td>      </tr>      <tr>       <td>USER()</td>       <td>当前用户</td>      </tr>      <tr>       <td>VERSION()</td>       <td>版本信息</td>      </tr>     </tbody>    </table>    <h2>聚合函数</h2>    <table>     <thead>      <tr>       <th>函数名称</th>       <th>描述</th>      </tr>     </thead>     <tbody>      <tr>       <td>AVG()</td>       <td>平均值</td>      </tr>      <tr>       <td>COUNT()</td>       <td>计数</td>      </tr>      <tr>       <td>MAX()</td>       <td>最大值</td>      </tr>      <tr>       <td>MIN()</td>       <td>最小值</td>      </tr>      <tr>       <td>SUM()</td>       <td>求和</td>      </tr>     </tbody>    </table>    <h2>加密函数</h2>    <table>     <thead>      <tr>       <th>函数名称</th>       <th>描述</th>      </tr>     </thead>     <tbody>      <tr>       <td>MD5()</td>       <td>信息摘要算法</td>      </tr>      <tr>       <td>PASSWORD()</td>       <td>密码算法</td>      </tr>     </tbody>    </table>    <h2>自定义函数</h2>    <p>用户自定义函数（user-defined function，UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。UDF是对MySQL扩展的一种途径。</p>    <h3>必要条件</h3>    <ul>     <li>参数：可以有零个或多个</li>     <li>返回值：只能有一个</li>    </ul>    <p>参数和返回值没有必然的联系。</p>    <h3>创建自定义函数</h3>    <p>CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body</p>    <p>函数体（routine_body）</p>    <ul>     <li>函数体由合法的SQL语句构成；</li>     <li>函数体可以是简单的SELECT或INSERT语句；</li>     <li>函数体如果为复合结构则使用BEGIN…END语句；</li>     <li>复合结构可以包含声明，循环，控制结构。</li>    </ul>    <p>示例</p>    <pre>  <code class="language-sql"># 不带参数  CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');    # 带参数  CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED) RETURNS FLOAT(10, 2) UNSIGNED RETURN (num1 + num2) / 2;    # 具有复合结构函数体  # 可能需要使用DELIMITER命令修改分隔符  CREATE FUNCTION f3(username VARCHAR(20)) RETURNS INT UNSIGNED   BEGIN   INSERT test(username) VALUES(username);  RETURN LAST_INSERT_ID();  END  </code></pre>    <h2>2 存储过程</h2>    <p><img src="https://simg.open-open.com/show/e05a5de502c463e0722c81c3287fc20e.png"> 存储过程是SQL语句和控制语句的预编译集合，以一个名称存储作为一个单元处理。可以由用户调用执行，允许用户声明变量以及进行流程控制。存储过程可以接收输入类型的参数，也可以接收输出类型的参数，并可以存在多个返回值。执行效率比单一的SQL语句高。</p>    <h2>优点</h2>    <ul>     <li>增强SQL语句的功能和灵活性</li>    </ul>    <p>在存储过程中可以写控制语句具有很强的灵活性，可以完成复杂的判断及较复杂的运算。</p>    <ul>     <li>实现较快的执行速度</li>    </ul>    <p>如果某一操作包含了大量的SQL语句，那么这些SQL语句都将被MySQL引擎执行语法分析、编译、执行，所以效率相对过低。而存储过程是预编译的，当客户端第一次调用存储过程时，MySQL的引擎将对它进行语法分析、编译等操作，然后把这个编译的结果存储到内存中，所以说第一次使用的时候效率和以前是相同的。但是以后客户端再次调用这个存储过程时，直接从内存中执行，所以说效率比较高，速度比较快。</p>    <ul>     <li>减少网络流量</li>    </ul>    <p>如果通过客户端每一个单独发送SQL语句让服务器来执行，那么通过http协议来提交的数据量相对来说较大。</p>    <h2>创建</h2>    <pre>  <code class="language-sql">CREATE [DEFINER = {user|CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[, ...]]) [characteristic ...] routine_body  </code></pre>    <p>proc_parameter :</p>    <p>[IN | OUT | INOUT] param_name type</p>    <p>参数：</p>    <p>IN ，表示该参数的值必须在调用存储过程时指定。</p>    <p>OUT ，表示该参数值可以被存储过程改变，并且可以返回。</p>    <p>INOUT ，表示该参数的调用时指定，并且可以被改变和返回。</p>    <p>特性：</p>    <p>COMMENT 注释</p>    <p>CONTAINS SQL 包含SQL语句，但不包含读或写数据的语句。</p>    <p>NO SQL 不包含SQL语句。</p>    <p>READS SQL DATA 包含读写数据的语句。</p>    <p>MODIFIES SQL DATA 包含写数据的语句。</p>    <p>SQL SECURITY {DEFINER | INVOKER} 指明谁有权限来执行。</p>    <h2>过程体</h2>    <ul>     <li>过程体由合法的SQL语句构成；</li>     <li>过程体可以是任意SQL语句；<br> 不能通过存储过程来创建数据表、数据库。可以通过存储过程对数据进行增、删、改、查和多表连接操作。</li>     <li>过程体如果为复合结构则使用BEGIN…END语句；</li>     <li>复合结构中可以包含声明、循环、控制结构。</li>    </ul>    <h2>调用</h2>    <pre>  <code class="language-sql">CALL sp_name ([parameter[, ...]])  CALL sp_name[()]  </code></pre>    <h2>删除</h2>    <pre>  <code class="language-sql">DROP PROCEDURE [IF EXISTS] sp_name  </code></pre>    <h2>修改</h2>    <pre>  <code class="language-sql">ALTER PROCEDURE sp_name [characteristic ...] COMMENT 'string'  | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}  | SQL SECURITY {DEFINER | INVOKER}  </code></pre>    <h2>存储过程与自定义函数的区别</h2>    <ul>     <li>存储过程实现的功能要复杂一些，而函数的针对性更强。</li>     <li>存储过程可以返回多个值，函数只能有一个返回值。</li>     <li>存储过程一般独立执行，函数可以作为其他SQL语句的组成部分来实现。</li>    </ul>    <p>示例：</p>    <pre>  <code class="language-sql"># 创建不带参数的存储过程  CREATE PROCEDURE sp1() SELECT VERSION();    # 创建带有IN类型参数的存储过程(users为数据表名)  # 参数的名字不能和数据表中的记录名字一样  CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)  BEGIN  DELETE FROM users WHERE id = p_id;  END    # 创建带有IN和OUT类型参数的存储过程(users为数据表名)  CREATE PROCEDURE removeUserAndReturnUserNumsById(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)  BEGIN  DELETE FROM users WHERE id = p_id;  SELECT COUNT(id) FROM users INTO userNums;  END    # 创建带有多个OUT类型参数的存储过程(users为数据表名)  CREATE PROCEDURE removeUserAndReturnInfosByAge(IN p_age SMALLINT UNSIGNED, OUT delUser SMALLINT UNSIGNED,  OUT userNums SMALLINT UNSIGNED)  BEGIN  DELETE FROM users WHERE age = p_age;  SELECT ROW_COUNT INTO delUser;  SELECT COUNT(id) FROM users INTO userNums;  END  </code></pre>    <h2>3 存储引擎</h2>    <p>MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。</p>    <p>每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。</p>    <ul>     <li> <p>锁</p> <p>共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。</p> <p>排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。</p> </li>     <li> <p>锁颗粒</p> <p>表锁：是一种开销最小的锁策略。</p> <p>行锁：是一种开销最大的锁策略。</p> </li>     <li> <p>并发控制</p> <p>当多个连接记录进行修改时保证数据的一致性和完整性。</p> </li>     <li> <p>事务</p> <p>事务用于保证数据库的完整性。</p> </li>    </ul>    <p>举例：用户银行转账</p>    <p>用户A 转账200元 用户B</p>    <p>实现步骤：</p>    <p>1）从当前账户减掉200元（账户余额大于等于200元）。</p>    <p>2）在对方账户增加200元。</p>    <p>事务特性：</p>    <p>1）原子性（atomicity）</p>    <p>2）一致性（consistency）</p>    <p>3）隔离性（isolation）</p>    <p>4）持久性（durability）</p>    <ul>     <li> <p>外键</p> <p>是保证数据一致性的策略。</p> </li>     <li> <p>索引</p> <p>是对数据表中一列或多列的值进行排序的一种结构。</p> </li>    </ul>    <h2>类型</h2>    <p>MySQL主要支持以下几种引擎类型：</p>    <ul>     <li>MyISAM</li>     <li>InnoDB</li>     <li>Memory</li>     <li>CSV</li>     <li>Archive</li>    </ul>    <p>各类存储引擎特点</p>    <table>     <thead>      <tr>       <th>特点</th>       <th>MyISAM</th>       <th>InnoDB</th>       <th>Memory</th>       <th>Archive</th>      </tr>     </thead>     <tbody>      <tr>       <td>存储限制</td>       <td>256TB</td>       <td>64TB</td>       <td>有</td>       <td>无</td>      </tr>      <tr>       <td>事务安全</td>       <td>-</td>       <td>支持</td>       <td>-</td>       <td>-</td>      </tr>      <tr>       <td>支持索引</td>       <td>支持</td>       <td>支持</td>       <td>支持</td>       <td> </td>      </tr>      <tr>       <td>锁颗粒</td>       <td>表锁</td>       <td>行锁</td>       <td>表锁</td>       <td>行锁</td>      </tr>      <tr>       <td>数据压缩</td>       <td>支持</td>       <td>-</td>       <td>-</td>       <td>支持</td>      </tr>      <tr>       <td>支持外键</td>       <td>-</td>       <td>支持</td>       <td>-</td>       <td>-</td>      </tr>     </tbody>    </table>    <p>CSV:实际上是由逗号分隔的数据引擎，在数据库子目录为每一个表创建一个 .csv 的文件，这是一种普通的文本文件，每一个数据行占用一个文本行。不支持索引。</p>    <p>BlackHole：黑洞引擎，写入的数据都会消失，一般用于做数据复制的中继。</p>    <p>MyISAM：适用于事务的处理不多的情况。</p>    <p>InnoDB：适用于事务处理比较多，需要有外键支持的情况。</p>    <p>索引分类：普通索引、唯一索引、全文索引、btree索引、hash索引…</p>    <h2>修改存储引擎</h2>    <ul>     <li>通过修改MySQL配置文件<br> default-storage-engine=engine_name</li>     <li>通过创建数据表命令实现<br> CREATE TABLE table_name(...)ENGINE=engine_name</li>     <li>通过修改数据表命令实现<br> ALTER TABLE table_name ENGINE[=]engine_name</li>    </ul>    <h2>4 管理工具</h2>    <ul>     <li> <p>phpMyAdmin</p> <p>需要有PHP环境</p> </li>     <li> <p>Navicat</p> </li>     <li>MySQL Workbench</li>    </ul>    <p> </p>    <p>来自：http://chars.tech/2017/05/29/mysql-advanced-study/</p>    <p> </p>