🛢MySQLBasic
MySQL环境配置以及常见问题
关系型数据库常见 MySQL、Oracle 与 SQL Server，非关系型数据库有键值型数据库 Redis、文档型数据库 Mongodb、搜索引擎数据库 ES 与 Solr、列式数据库 HBase、图形数据库 InfoGrid。
快速上手
安装与卸载
# 安装
# MySQL Server 提供数据储存、数据服务
$ brew install mysql
# MySQL Workbench: 可视化MySQL管理工具-方便操作存储的数据
$ brew install --cask mysqlworkbench
# 或官方安装文件安装
...
# 卸载
$ mysql --version
# mysql Ver 8.0.28 for macos12.2 on x86_64 (Homebrew)
$ sudo rm /usr/local/mysql
$ sudo rm -rf /usr/local/mysql*
$ sudo rm -rf /Library/StartupItems/MySQLCOM
# 因为有 InstallHisory.plist 注意不要把 Receipts 全删 -- 概率没有 mysql*
# /Library/Receipts 通常为 user 在 APP Store 中下载的 receipts
$ sudo rm -rf /Library/Receipts/mysql*
常用路径
- 安装完成配置环境变量。
$ vi ~/.zshrc
# mysql
export PATH="/usr/local/mysql/bin:$PATH"
alias mysql='/usr/local/mysql/bin/mysql'
# 更新配置
$ source ~/.zshrc
- 安装包安装完成可通过在系统偏好设置中 mysql 项的 configuration 栏查询具体各内容的安装存在位置。
# 安装文件安装,非homebrew
# Base Directory
/usr/local/mysql
# Data Directory
/usr/local/mysql/data
# Plugin Directory
/usr/local/mysql/lib/plugin
# Keyring Data File
/usr/local/mysql/keyring/keyring
# Error Log
/usr/local/mysql/data/mysqld.local.err
# PID File
/usr/local/mysql/data/mysqld.local.pid
- mac 上使用 homebrew 安装的配置文件存放路径。
$ mysqld --help --verbose | more
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
# 注意 /usr/local/etc/my.cnf !!
常用命令
- 前置命令
# ---准备---
# 1.线程查看 MySQL 是否启动
$ ps -ef | grep mysql
# 2. brew 查看启动程序
$ brew services list
# --启动--
# 1.开始/停止/重启 MySQL 服务
$ sudo /usr/local/MySQL/support-files/mysql.server start/stop/restart
# 2. brew 开始/停止 MySQL 服务
$ brew services start/stop mysql
$ mysql.server start/stop
# ---登入登出---
# 登陆 mysql
$ mysql -u root -p
# 退出 mysql
mysql> exit;
- 导入 .sql 文件
创建以导入的 .sql 文件名命名的新数据库。（此处以 mydb 为例）
# 登入数据库
# 常用格式 => mysql -u root -p xxx -h localhost -P 3306
$ mysql -u root -p
# 使用数据库(前提存在此数据库)
$ mysql>use mydb(使用那个数据库的名称)
# 导入 .spl 文件
$ mysql> source /Users/zsxzy/Desktop/.../db/mydb.sql
在测试文件是否成功导入时，可能因权限问题报错，在确保数据安全的情况下粗暴解决：sudo chmod -R 777 /usr/local/mysql/data/...
SQL Structured Query Language
功能分类
DDL Data Definition Languages 数据定义语言用于定义库、表、视图、索引等数据库不同的对象，还可以用来创建、删除、修改数据库和数据表的结构。
CREATE|DROP|ALTER|TRUNCATE|RENAME
DML Data Manipulation language 数据操作语言用于添加、删除、更新和查询数据库记录，并检查数据完整性。
INSERT|DELETE|UPDATE|SELECT
DCL Data Control language 数据控制语言用于定义库、表、字段、用户的访问权限和安全级别。
GRANT|REVOKE|COMMIT|ROLLBACK|SAVEPOINT
因查询语句的使用频繁，故很多人把查询语句单拎出来作为一类 DQL 数据查询语言。还有单独的将 COMMIT、ROLLBACK 取出来称为 TCL Transaction Control Language 事务控制语言。
规则与规范
SQL 可以写在一行或者多行。为了提高可读性各子句分行写，必要时使用缩进。每条命令以 ; 或 \g 或 \G 结束。对于字符串和时间类型的数据可以使用单引号 ''，但是列的别名建议使用双引号 ""，且不建议省略。
建议数据库名、表名、表别名、字段名、字段别名等都小写；SQL 关键字、函数名、绑定变量等都大写。
数据库命名不能中文和空格。PK primary key 主键唯一标识、NN not null 值不允许为空、UQ Unique 值唯一、AI Auto Increment 值自动增长。
单行注释可以使用 # 注释符，直接后边接注释内容。也可以使用 -- 注释符，注意这里有一个空格才能生效。行注释使用 /* */注释符。
MySQL 中可直接执行 select ...; 不加 from table;，而 Oracle 中需满足 select * from table; 的结构，故引入虚拟表 DUAL。MySQL5.5 以上版本也引入了虚拟表 DUAL，但执行 select * from dual; 会报错。
按提出时间标准命名的 SQL92 和 SQL99 对应着 SQL 的两个主要标准 SQL-2 和 SQL-3。SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。可以把自然连接理解为 SQL92 中的等值连接。其会帮助自动查询两张连接表中所有相同的字段，然后进行等值连接。SQL99 还支持使用 USING 指定表里的同名字段等值连接。与自然连接不同，USING 括号中指定了具体的相同字段名称。使用 JOIN USING 可以简化 JOIN ON 的等值连接。
# 表employees和departments存在两个字段相同
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id AND e.manager_id = d.manager_id;
# 使用自然连接
SELECT employee_id,last_name,department_name FROM employees e NATURAL JOIN departments d;
# 92语法
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d WHERE e.department_id = d.department_id;
# using => 不适用于自连接
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d USING (department_id);
SQL 基础
- 列的别名
as 全称 alias 别名，可以省略，同时列的别名可以使用一对双引号包裹。如果列名称包含空格，要求使用双引号或方括号。需要注意，别名可以在 ORDER BY 使用，不能在 WHERE 使用。
SELECT employee_id emp_id,last_name AS lname,department_id "dept_id",salary*12 "annual sal"
FROM employees;
+--------+-------------+---------+
| emp_id | lname | dept_id |
+--------+-------------+---------+
| ... | .... | ... |
+--------+-------------+---------+
# 错误示范
SELECT employee_id,salary,salary * 12 annual_sal FROM employees WHERE annual_sal > 81600;
- 着重号
通常情况下，表名、字段加上着重号是没有问题的，但是一般不需要。如果出现表名、字段名与关键字重名的情况，需要明确加上着重号表明不是关键字。
SELECT * FROM `order`;
- 查询常数
在开发场景中，若需将不存在于表中字段里的数据加在结果显示中，可以考虑使用查询常数。
SELECT 'zs',123,employee_id,last_name FROM employees;
- 显示表结构
刻画表创建时，字段的相关信息。
DESCRIBE employees;
DESC employees;
- 算术运算符
在 SQL 中，+ 没有连接作用，仅表示加法运算，此时会将字符串转化为数值（隐式转换）。
SELECT 100 + '1' FROM DUAL; # 101
SELECT 100 + 'a' FROM DUAL; # 此时将不可转换的字符串转化为0
SELECT 100 + NULL FROM DUAL; # NULL参与运算结果均为 null
- 比较运算符
字符串存在隐式转换，如果转换数值不成功，则看作 0。两边都是字符串的情况下，按照 ANST 规则进行比较。UTF-8 向下兼容了 ASCII 码，比较转换后数值是否一致。只要有 NULL 参与的比较，结果都是 NULL。
mysql> SELECT 1 = 2,1 != 2,1 = '1',1 = 'a',0 = 'a' FROM DUAL;
+-------+--------+---------+---------+---------+
| 1 = 2 | 1 != 2 | 1 = '1' | 1 = 'a' | 0 = 'a' |
+-------+--------+---------+---------+---------+
| 0 | 1 | 1 | 0 | 1 |
+-------+--------+---------+---------+---------+
mysql> SELECT 'a' = 'a','ab' = 'ab','a' = 'b' FROM DUAL;
+-----------+-------------+-----------+
| 'a' = 'a' | 'ab' = 'ab' | 'a' = 'b' |
+-----------+-------------+-----------+
| 1 | 1 | 0 |
+-----------+-------------+-----------+
mysql> SELECT 1 = NULL,NULL = NULL FROM DUAL;
- 安全等于运算符
安全等于运算符 <=> 与等于运算符 = 的作用是相似的，唯一的区别是 <=> 可以用来对 NULL 进行判断。在两个操作数均为 NULL 时，其返回值为 1，而不是为 NULL；当一个操作数为 NULL，其返回值为 0，不为 NULL。
mysql> SELECT 1 <=> 2,1 <=> '1',0 <=> 'a' FROM DUAL;
+---------+-----------+-----------+
| 1 <=> 2 | 1 <=> '1' | 0 <=> 'a' |
+---------+-----------+-----------+
| 0 | 1 | 1 |
+---------+-----------+-----------+
mysql> SELECT 1 <=> NULL, NULL <=> NULL FROM DUAL;
+------------+---------------+
| 1 <=> NULL | NULL <=> NULL |
+------------+---------------+
| 0 | 1 |
+------------+---------------+
# 查询表中 commission_pct 为 NULL 的字段
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct = NULL; # 没有数据
- BETWEEN AND 与 IN
BETWEEN AND 区分条件上下界，不可交换，查询条件范围内包含边界的数据。IN 则是在 set 范围中离散的查找匹配符合的数据。
- LIKE
模糊匹配 LIKE 常搭配不确定个数的字符 % 与代表一个不确定的字符 _ 使用。
SELECT last_name FROM employees WHERE last_name LIKE '%a%';
SELECT last_name FROM employees WHERE last_name LIKE '%a%' OR last_name LIKE '%e%';
SELECT last_name FROM employees WHERE last_name LIKE '_a'; # 模糊查询 _a
SELECT last_name FROM employees WHERE last_name LIKE '_\_a%'; # 模糊查询 _ a
SELECT last_name FROM employees WHERE last_name LIKE '_$_a%' ESCAPE '$';
BUG 解决
Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES)
异常指令err: AccessDeniedError [SequelizeAccessDeniedError]: Access denied for user 'root'@'localhost' (using password: YES)
此问题是 配置的数据库密码 与 数据库设置的密码 不同所导致，应在项目配置数据库文件脚本找到对应的配置代码进行修改，如 default.json 或 config 中的 mysql.js（登录文件）。要是这里 using password 后接的是 No 。那么应从 权限 以及 依赖包 进行考虑。
Your connection attempt failed for user 'root' to the MySQL server at localhost:3306:
Unable to connect to localhost:3306
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
ERROR! The server quit without updating PID file
要确保完全卸载旧版本再去安装新版 Mysql。mysql.sock 文件用于 socket 连接的文件，只有守护进程启动起来这个文件才存在。SOF传送门。
ERROR! The server quit without updating PID file (/usr/local/var/mysql/xxx.local.pid).
此时退出会有两种情况考虑：1.权限给定、2.上版mysql未删除彻底
# 开启 Node 端口服务时协议不匹配
ConnectionError [SequelizeConnectionError]: Client does not support authentication protocol requested by server; consider upgrading MySQL client
MySQL 8.0 使用 caching_sha2_password 作为默认身份验证插件，而不是 MySQL 5.7 默认 mysql_native_password。而最新的 mysql 模块并未完全支持 MySQL8 的加密方式。此时考虑重新修订用户 root 的密码，指定 mysql 模块支持的加密方式。
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new password'
# 默认新协议的密码更改方式
# ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
# 在更更改密码时出现如下错误
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
由于密码要求修改失败，此时检查密码要求变量。
$ SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
修改 validate_password_policy 的 MEDIUM 为 LOW 。但是需要注意，若直接在此处设置 set global validate_password.xxx=${refixval} 的话，加密配置会在下一次启动 MySQL 时失效。应在 /usr/local/etc/my.cnf 中重新定义加密规则。
[mysqld]
validate_password_length=6
validate_password_policy=LOW
数据库连接异常
异常指令err: ConnectionError [SequelizeConnectionError]: Connection lost: The server closed the connection.
此时项目后端接口使用 mysql 连接字符串是 localhost。在将连接字符串替换成 127.0.0.1 可以成功连接。localhost 是走 socket 127.0.0.1 是走 3306 端口。配置反推两者应该是相反的连接成功与失败结果，这里也不应该出现 localhost 不行而 127.0.0.1 可以。所以考虑测网络环境问题，测试如下：
$ ping localhost
PING localhost.lan (198.18.2.xxx): 56 data bytes
64 bytes from 198.18.2.xxx: icmp_seq=0 ttl=63 time=0.182 ms
64 bytes from 198.18.2.xxx: icmp_seq=1 ttl=63 time=0.436 ms
根据上述测试得出结果：由于科学上网导致网络连通问题。因为 localhost 是一个域名，只有把域名解析为 127.0.0.1 才能传输。既然都解析不成 127.0.0.1 ，那么连接就是无稽之谈。
$ ping localhost
PING localhost.lan (127.0.0.1): 56 data bytes
64 bytes from 127.0.0.1: icmp_seq=0 ttl=64 time=0.051 ms
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.148 ms
安装 mysql 不成功的原因通常可能是没有将其旧版完全卸载，导致存在残余文件与安装文件冲突，建议彻底删除后重试。
# 错误信息
2019-08-08T03:57:00.919252Z 0 [System] [MY-013169] [Server] /usr/local/Cellar/mysql/8.0.25_1/bin/mysqld (mysqld 8.0.25) initializing of server in progress as process 2624
2019-08-08T03:57:00.921626Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2019-08-08T03:57:00.921638Z 0 [ERROR] [MY-013236] [Server] The designated data directory /usr/local/var/mysql/ is unusable. You can remove all files that the server added to it.
2019-08-08T03:57:00.921780Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-08-08T03:57:00.922020Z 0 [System] [MY-010910] [Server] /usr/local/Cellar/mysql/8.0.25_1/bin/mysqld: Shutdown complete (mysqld 8.0.25) Homebrew.
Warning: The post-install step did not complete successfully
# 残余文件存在位置
/usr/local/var
/usr/local/etc/my.*
mysql5 版本向指定的数据库中添加中文信息不成功，导致命令行操作 sql 乱码问题。
mysql> INSERT INTO <table_name> VALUES(...,"<中文>",...)
ERROR 1366 (HY000): Incorrect string value: '\x[][]' for column 'xx' at row 1
# 查看表信息
mysql> show create table <table_name>;
ENGINE=InnoDB DEFAULT latin1
# 查看编码命令
mysql> show variables like 'character_%';
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 # 检查此处是否为latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 # 检查此处是否为latin1 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/Cellar/mysql/8.0.28_1/share/mysql/charsets/ |
+--------------------------+--------------------------------------------------------+
mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
# 修改 mysql 配置文件 my.cnf / my.ini
# mac homebrew
$ mysqld --help --verbose | more
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
# my.cnf 样例
[mysql]
...
default_character_set=utf8
[mysqld]
#
# * Basic Settings
#
user = mysql
# pid-file = /var/run/mysqld/mysqld.pid
# socket = /var/run/mysqld/mysqld.sock
# port = 3306
# datadir = /var/lib/mysql
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size = 16M
# max_allowed_packet = 64M
# thread_stack = 256K
# thread_cache_size = -1
myisam-recover-options = BACKUP
# max_connections = 151
# table_open_cache = 4000
log_error = /var/log/mysql/error.log
max_binlog_size = 100M
secure_file_priv=''
...
character-set-server=utf-8
collation-server=utf8_general_ci
结束
本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议，转载请注明出处！