拆解 MySQL 的高阶使用与概念
Maximilian6
7年前
<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>