MySQL JSON Table

今天在 Stack Overflow 上看到这样一个问题: How SUM the result of an array returned by jsn_extract? 参考: https://stackoverflow.com/questions/31731513/mysql-labs-json-native-type-how-sum-the-result-of-an-array-returned-by-jsn-extr https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html  524 total views

 524 total views

Published

MySQL 窗口函数

MySQL 8.0 引入了 窗口函数(window functions) ,我们看一下都有哪些功能。 Name Description CUME_DIST() Cumulative distribution value DENSE_RANK() Rank of current row within its partition, without gaps FIRST_VALUE() Value of argument from first row of window frame LAG() Value of argument from row lagging current…

 854 total views

Published

【翻译】MySQL 优化概述

原文链接:https://dev.mysql.com/doc/refman/8.0/en/optimize-overview.html 数据库性能取决于数据库层面的多个因素,例如表、查询和配置。这些软件构造会导致硬件层面的 CPU 和 I/O 操作,你必须尽可能最小化并提高效率。当你刚开始优化数据库性能的时候,首先你要学习软件方面的高级规则和准则,和使用系统时间测量性能。当你成为专家后,你将了解更多的内部细节,并开始测量 CPU 周期和 I/O 操作等指标。 普通用户的目标是从现有的软件和硬件配置中获得最佳的数据库性能。高级用户则会寻找机会来改进 MySQL 软件本身,或者开发自己的存储引擎和硬件设备来扩展 MySQL 生态系统。 数据库层面优化 使数据库应用变快的最重要因素是其基本设计: 表结构是否正常?特别是列是否具有正确的数据类型,并且每个表是否具有适用于工作类型的相应列?例如,需要频繁更新的应用程序通常有许多表,但列很少,而分析大量数据的应用程序通常只有很少的表,但有许多列。 是否使用了正确的索引来提高查询效率? 你是否为每个表选择适当的存储引擎,并且充分利用每种存储引擎的优势和功能?特别是选择事务性存储引擎(如 InnoDB)还是非事务性存储引擎(如 MyISAM)对于性能和可伸缩性非常重要。 注意 InnoDB 是新表的默认存储引擎。实际上,先进的 InnoDB 性能特点意味着 InnoDB 表通常优于更简单的 MyISAM 表,尤其是对于繁忙的数据库。 是否每个表都使用适当的行格式?此选项还取决于用于表的存储引擎。特别是,压缩表占用的磁盘空间较少,因此读取和写入数据所需的磁盘 I/O 更少。压缩可用于具有 InnoDB 表的所有类型的工作负载以及只读的 MyISAM 表。 应用程序是否使用适当的锁定策略?例如,在可能的情况下允许共享访问,以便数据库操作可以同时运行,并在适当时请求独占访问,以便关键操作获得最高优先级。同样,存储引擎的选择也非常重要。InnoDB 存储引擎无需您参与即可处理大多数锁定问题,从而在数据库中实现更好的并发性,并减少代码的实验和调优量。…

 19,093 total views,  1 views today

Published

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

使用 PHP 连接 MySQL 8.0 的时候,可能会发生如下错误: 错误原因是 MySQL 8 默认使用了新的密码验证插件:caching_sha2_password,而之前的 PHP (mysqlnd) 版本无法支持这种验证。 有两种方法可以解决这个问题。 升级 PHP 版本 使用 mysql_native_password 方法一:升级 PHP 版本 PHP 7.2.8 版本已经可以支持 caching_sha2_password,直接连接MySQL 8。 但是需要注意的是:不知道是 Bug 还是 Feature,最新的 PHP 7.2.12 版本 和 PHP 7.3.0 版本都不支持 caching_sha2_password。 方法二:使用…

 1,533 total views,  1 views today

Published

Please Use utf8mb4

最近遇到这样一个问题,MySQL 执行报错: 查询出错 (1366): Incorrect string value: ‘\xF0\x9F\x98\x84\xF0\x9F…’ for column ‘nickName’ at row 1 执行的 SQL 语句是: user 表中 nickName 字段的定义是: 照理说 utf8mb4 编码存储表情符号是没有问题的,于是参照 Stack Overflow 的回答把 vachar(255) 改成 varbinary(255),的确能够执行成功了。 但是问题接踵而来:查询的时候需要不区分大小写,然而 varbinary 类型不支持 case insenstive。 折腾了一番之后再回来看这个问题,一开始就不是 varchar 和 varbinary 的问题,而是 MySQL 连接的编码设置错了。 把 MySQL…

 1,033 total views,  1 views today

Published

MySQL OR 查询优化

最近在优化 SQL 语句的时候发现一个问题:MySQL 在执行 OR 查询的时候,即使有多个字段能够使用索引,也只会使用其中一个字段的索引。然后在网上找到了如下答案,贴在这里以备参考。 注意:以下内容完全拷贝自 Stack Overflow。 1. The typical way to break up OR predicates is with UNION. 2. UNION is UNION DISTINCT by default. Duplicate rows are removed as part of the UNION construct. If one…

 667 total views

Published

MySQL federated storage engine

1. 启动 federated 引擎 show engines; 首先我们查看一下当前已经安装的存储引擎,默认情况为 Engine 列表里面有 FEDERATED,但是Support 为 NO。这时我们需要修改 MySQL 配置文件。 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 在文件末尾添加如下两行。 [mysqld] federated 然后重启 MySQL。 sudo systemctl restart mysql.service 再查看一下 FEDERATED 引擎的 Support 就变成 YES 了。 如果存储引擎的列表里面没有找到 FEDERATED,那么你可能就需要考虑重新安装 MySQL 了。 2. 创建 federated…

 940 total views

Published

MySQL 5.7 JSON 简介

从 MySQL 5.7.8 版本起,MySQL 开始支持原生 JSON 格式。 1. 存储 JSON 数据 首先我们要把字段类型设置为 json,这样存储的时候才会检查插入的 JSON 数据是否符合规范,不符合规范会报错。至于如何生成 JSON 数据,我们可以使用 PHP 的 json_encode() 方法,也可以使用 MySQL 的 JSON_ARRAY() 和 JSON_OBJECT() 方法。 create table json_data(json_data json); insert into json_data(json_data) values (‘{“name”:”Victor Tang”,”phone”:”13866001386″,”email”:”tanghengzhi@live.com”}’); insert into json_data(json_data)…

 829 total views

Published

MySQL 5.7 group by

从MySQL 5.6 升级到 MySQL 5.7,我相信大家或多或少都遇到了一点 group by 的问题,也许是 `this is incompatible with sql_mode=only_full_group_by` 的报错,也许是 group by 查询出来的结果莫名其妙的和之前版本不一样。 在开始之前我们先来造一些数据: SET NAMES utf8mb4; CREATE TABLE `books` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, `press` varchar(128) COLLATE utf8mb4_unicode_ci…

 901 total views

Published

Migrating from MariaDB to MySQL

网上从 MySQL 迁移到 MariaDB 的教程很多,但是从 MariaDB 迁移到 MySQL 的教程却很少。最近刚好花时间把测试服务器上的 MariaDB 迁移到 MySQL 了,所以我打算记录一下操作过程,以供参考。 1. Backup your data 一定要备份数据!不要在生产环境操作! 一定要备份数据!不要在生产环境操作! 一定要备份数据!不要在生产环境操作! 重要的事情说三遍,请一定要再做好准备之后再继续以下操作。 2. Stop MariaDB 首先,停止 MariaDB 服务。 然后,卸载 MariaDB。 需要注意的是,使用 yum remove 不一定能够完全卸载干净,可以使用 rpm -qa 检查一下是否有遗漏。 3. Install MySQL MariaDB…

 688 total views

Published