云南省住房和城乡建设厅网站温州学校网站建设

当前位置： 首页 > news >正文

云南省住房和城乡建设厅网站,温州学校网站建设,注册资金,做网站广告网页1 系统变量1.1 查看系统变量可以使用以下命令查看 MySQL 中所有的全局变量信息。SHOW GLOBAL VARIABLES; MySQL 中的系统变量以两个“”开头。global 仅仅用于标记全局变量#xff1b;session 仅仅用于标记会话变量#xff1b;首先标记会话变量#xff0c;如果会话变量不存在…1 系统变量1.1 查看系统变量可以使用以下命令查看 MySQL 中所有的全局变量信息。SHOW GLOBAL VARIABLES; MySQL 中的系统变量以两个“”开头。global 仅仅用于标记全局变量session 仅仅用于标记会话变量首先标记会话变量如果会话变量不存在则标记全局变量。1.2 设置系统变量可以通过以下方法设置系统变量修改 MySQL 源代码然后对 MySQL 源代码重新编译该方法适用于 MySQL 高级用户这里不做阐述。在 MySQL 配置文件mysql.ini 或 mysql.cnf中修改 MySQL 系统变量的值需要重启 MySQL 服务才会生效。在 MySQL 服务运行期间使用 SET 命令重新设置系统变量的值。服务器启动时会将所有的全局变量赋予默认值。这些默认值可以在选项文件中或在命令行中对执行的选项进行更改。更改全局变量必须具有 SUPER 权限。设置全局变量的值的方法如下SET global.innodb_file_per_tabledefault;SET global.innodb_file_per_tableON;SET global innodb_file_per_tableON;需要注意的是更改全局变量只影响更改后连接客户端的相应会话变量而不会影响目前已经连接的客户端的会话变量即使客户端执行 SET GLOBAL 语句也不影响。也就是说对于修改全局变量之前连接的客户端只有在客户端重新连接后才会影响到客户端。客户端连接时当前全局变量的值会对客户端的会话变量进行相应初始化。设置会话变量不需要特殊权限但客户端只能更改自己的会话变量而不能更改其它客户端的会话变量。设置会话变量的值的方法如下SET session.pseudo_thread_id5;SET session pseudo_thread_id5;SET pseudo_thread_id5;SET pseudo_thread_id 5;如果没有指定修改全局变量还是会话变量服务器会当作会话变量来处理。比如SET sort_buffer_size 50000;上面语句没有指定是 GLOBAL 还是 SESSION服务器会当做 SESSION 处理。使用 SET 设置全局变量或会话变量成功后如果 MySQL 服务重启数据库的配置就又会重新初始化。一切按照配置文件进行初始化全局变量和会话变量的配置都会失效。2 自定义变量用户自定义变量是一个容易被遗忘的MySQL特性但是如果能用的好发挥其潜力在某些场景可以写出非常高效的查询语句。在查询中混合使用过程化和关系化逻辑的时候自定义变量可能会非常有用。单纯的关系查询将所有的东西都当成无序的数据集合并且一次性操作它们。MySQL则采用了更加程序化的处理方式。MySQL的这种方式有它的弱点但如果能够熟练地掌握则会发现其强大之处而用户自定义变量也可以给这种方式带来很大的帮助2.1 设置自定义变量用户自定义变量是一个用来存储内容的临时容器在连接MySQL的整个过程中都存在可以使用下面的SET和SELECT语句来定义它们SET one : 1; SET min_actor : (SELECT MIN(actor_id) FROM sakila.actor); SET last_week : CURRENT_DATE - INTERVAL 1 WEEK;2.2 查看自定义变量然后可以在任何可以使用表达式的地方使用这些自定义变量SELECT … WHERE col last_week;在了解自定义变量的强大之前我们先来看看它自身的一些属性和限制看看在哪些场景下我们不能使用用户自定义变量使用自定义变量的查询无法使用查询缓存不能再使用常量或者标识符的地方使用自定义变量例如表名、列名和LIMIT子句中。用户自定义变量的生命周期是在一个连接中有效所以不能用它们来做连接间的通信。如果使用连接池或者持久化连接自定义变量可能让看起来毫无关系的代码发生交互。自定义变量的类型是一个动态类型。MySQL优化器在某些场景下可能会将这些变量优化掉这可能导致代码不按预想的方式运行。赋值的顺序和赋值的时间点并不总是固定的这依赖于优化器的决定。赋值符号 :的优先级非常低所以需要注意赋值表达式应该使用明确的括号。使用未定义变量不会产生任何语法错误如果没有意识到这一点非常容易犯错。2.3 自定义变量的运用2.3.1 优化排名语句使用自定义变量的一个特性是你可以在给一个变量赋值的同时使用这个变量即“左值”特性。例如SET rownum : 0; SELECT actor_id, rownum : rownum 1 AS rownum FROM actor order by actor_id LIMIT 3;这个例子的实际意义并不大它只是实现了一个和该表主键一样的列。不过我们可以把这当作一个排名。现在我们来看一个更复杂的用法。我们先编写一个查询获取演过最多电影的前10位演员然后根据他们的出演电影次数做一个排名如果出演的电影数量一样则排名相同。我们先编写一个查询返回每个演员参演电影的数量。SET curr_cnt : 0, prev_cnt : 0, rank : 0; SELECT actor_id, COUNT() as cnt FROM film_actor GROUP BY actor_id ORDER BY cnt DESC LIMIT 10;现在我们再把排名加上去这里看到有四个演员都参演了35部电影所以他们的排名应该是相同的。我们使用三个变量来实现一个用来记录当前的排名一个用来记录前一个演员的排名还有一个用来记录当前演员参演的电影数量。只有当前演员参演的电影的数量和前一个演员不同时排名才变化。我们试试下面的写法SELECT actor_id, curr_cnt : COUNT() AS cnt, rank : IF(prev_cnt curr_cnt, rank 1, rank) AS rank, prev_cnt : curr_cnt AS dummy FROM film_actor GROUP BY actor_id ORDER BY cnt DESC LIMIT 10;我们发现跟我们设想的不太一样。这里通过EXPLAIN我们看到将会使用临时表和文件排序所以可能是由于变量赋值的时间和我们预料的不同。使用SQL语句生成排名值通常需要做两次计算例如需要额外计算一次出演过相同数量电影的演员有哪些。使用变量则可一次完成—这对性能是一个很大的提升。针对这个案例另一个简单的方案是在FROM子句中使用子查询生成的一个中间的临时表SELECT actor_id, curr_cnt : cnt AS cnt, rank : IF(prev_cnt curr_cnt, rank 1, rank) AS rank, prev_cnt : curr_cnt AS dummy FROM ( SELECT actor_id, COUNT(*) AS cnt FROM film_actor GROUP BY actor_id ORDER BY cnt DESC LIMIT 10 ) as der;2.3.2 避免重复查询刚刚更新的数据如果在更新行的同学又希望获得该行的信息避免重复查询可以用变量巧妙的实现。例如我们的一个客户希望能够更高效地更新一条记录的时间戳同时希望查询当前记录中存放的时间戳是什么。简单地可以用下面的代码来实现UPDATE t1 SET lastUpdated NOW() WHERE id 1; SELECT lastUpdated FROM t1 WHERE id 1;使用变量我们可以按如下方式重写查询:UPDATE t1 SET lastUpdated NOW() WHERE id 1 AND now : NOW(); SELECT now;上面看起来仍然需要两个查询需要两次网络来回但是这里第二个查询无需访问数据表所以会快很多。2.3.3 统计更新和插入的数量INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1) ON DUPLICATE KEY UPDATEc1 VALUES(c1) (0 * (x : x 1));当每次由于冲突导致更新时对变量x自增一次然后表达式乘以0让其不影响更新的内容另外MySQL的协议会返回被更改的总行数所以不需要单独统计。2.3.4 确定取值的顺序使用用户自定义变量的一个最常见的问题就是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段。例如在SELECT子句中进行赋值然后再WHERE子句中读取变量则可能变量取值并不如你所想SET rownum : 0; SELECT actor_id, rownum : rownum 1 AS cnt FROM actor WHERE rownum 1;因为WHERE和SELECT是在查询执行的不同阶段被执行的。如果在查询中再加入ORDER BY的话结果可能会更不同SET rownum : 0; SELECT actor_id, rownum : rownum 1 AS cnt FROM actor WHERE rownum 1 ORDER BY first_name;这是因为ORDER BY 引入了文件排序而WHERE条件是在文件排序操作之前取值的所以这条查询会返回表中的全部记录。解决这个问题的办法是让变量的赋值和取值发生在执行查询的同一阶段SET rownum : 0; SELECT actor_id, rownum AS rownum FROM actor WHERE (rownum : rownum 1) 1;2.3.5 编写偷懒的UNION假设需要编写一个UNION查询其第一个子查询作为分支条件先执行如果找到了匹配的行则跳过第二个分支。例如先在一个频繁访问的表查找热数据找不到再去另外一个较少访问的表查找冷数据。SELECT id FROM users WHERE id 123; UNION ALL SELECT id FROM users_archived WHERE id 123;上面的查询可以工作但是无论第一个表找没找到都会在第二个表再找一次如果使用变量的话可以很好地规避这个问题。SELECT GREATEST(found : -1, id) AS id, users AS which_tbl FROM users WHERE id 1 UNION ALLSELECT id, users_archivedFROM users_archived WHERE id 1 AND found IS NULL UNION ALL SELECT 1, reset FROM DUAL WHERE (found : NULL) IS NOT NULL;2.3.6 用户自定义变量的其他用处通过一些实践可以了解所有用户自定义变量能够做的有趣的事情例如下面这些用法查询运行时计算总数和平均值模拟GROUP语句中的函数FIRST()和LAST()S对大量数据做一些数据计算计算一个大表的MD5散列值编写一个样本处理函数模拟读/写游标在SHOW语句的WHERE子句中加入变量值