‹ 返回笔记 Esc数据库·

MySQL 问题与修复

MySQL 关联随机查询 SELECT a., b. FROM a LEFT JOIN b ON a.id = b.info_id AND RAND() < 0.5; 这个查询将使用RAND()函数来…

MySQL 关联随机查询

SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.info_id AND RAND() < 0.5; 这个查询将使用RAND()函数来随机关联 b 表中的数据，只有当RAND()函数返回值小于 0.5 时才会进行关联。这样可以让每次查询的结果都不同，从而达到随机的效果。可以根据需要修改RAND()函数的参数来控制随机程度。

MySQL Like查询大小写敏感

在 MySQL 中，LIKE 默认是不区分大小写的，但它依赖于表或列的排序规则（collation）。默认的排序规则通常是区分大小写的，例如 utf8_general_ci 中的 ci 表示 “case insensitive”（不区分大小写）。因此，LIKE 操作符通常会忽略大小写。

  • 不区分大小写的查询示例：
1SELECT * FROM users WHERE name LIKE 'john%';
  • 强制区分大小写的查询：
1SELECT * FROM users WHERE BINARY name LIKE 'john%';

在 PostgreSQL 中，LIKE 默认是区分大小写的。如果要忽略大小写，可以使用 ILIKE 操作符，它是 LIKE 的大小写不敏感版本。

不区分大小写的查询示例：

1SELECT * FROM users WHERE name ILIKE 'john%';

这会匹配 John, john, JOHN 等。

MySQL INSERT IGNORE 语法

1INSERT IGNORE INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...)

与普通的 INSERT INTO 相比，INSERT IGNORE 的区别在于：

  • 当插入的记录违反了唯一约束（UNIQUE）、主键约束（PRIMARY KEY）或其他错误时，不会抛出错误，而是自动忽略这条记录。
  • 如果没有错误，就正常插入。

当使用 INSERT IGNORE 时，以下情况会被忽略：

  • 插入的主键或唯一键重复；
  • 插入的数据违反 NOT NULL，但列允许默认值；
  • 插入的数据违反外键约束（某些情况下也会被忽略）；
  • 数据类型不匹配但可以自动转换。

MySQL 本地无法以 127.0.0.1 连接

1mysql -h 127.0.0.1 -P 3306 -u root -p
2Enter password:
3ERROR 1698 (28000): Access denied for user 'root'@'localhost'

因为 MySQL 在某些 Linux 发行版（尤其是 Ubuntu / Debian 系列）中，默认对 root@localhost 使用 socket 验证（auth_socket / unix_socket） 而不是密码验证。也就是说，仅允许操作系统用户 root（即 sudo 权限用户）直接通过 Unix socket 登录而不使用密码，而非通过 TCP + 密码的方式登录。

  1. 先切换到系统 root 权限（或用 sudo）进入 MySQL：
1sudo mysql
  1. 查看当前 plugin 配置（确认 root 是用哪个插件认证）：
1SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

你可能会看到 plugin 为 auth_socket 或 unix_socket。

  1. 修改 root 用户认证方式为基于密码（mysql_native_password 或 caching_sha2_password，取决于 MySQL 版本）：

对于 MySQL 5.7 及以上或 MySQL 8：

1ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';

或者在 MySQL 8 中可能使用：

1ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_new_password';

然后：

1FLUSH PRIVILEGES;

MySQL 密码忘记重置密码

  1. 停止MySQL服务
1brew services stop mysql
  1. 安全模式启动MySQL服务
1sudo mysqld_safe --skip-grant-tables &
  1. 使用mysql命令行连接到MySQL
1mysql -u root
  1. 重置密码
1UPDATE mysql.user SET authentication_string=null WHERE User='root' AND Host='localhost';
2ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

如果仍然无法使用 mysql_native_password，你可以尝试切换到其他认证插件，如 caching_sha2_password，这是 MySQL 8.0 的默认认证插件：

1ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '123456';
1ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
  1. 刷新权限并退出
1FLUSH PRIVILEGES;
2exit;
  1. 停止安全模式
1sudo mysqladmin shutdown

MySQL 外部连接速度很慢

MySQL 数据库在外部连接时速度特别慢，但本地连接正常。通过检查 MySQL 错误日志发现，系统存在大量的 DNS 解析失败警告：

1IP address '192.168.23.100' could not be resolved: Temporary failure in name resolution

这表明 MySQL 默认会对每个连接的客户端IP地址进行反向DNS解析（PTR查询），当DNS服务器响应缓慢或无法解析时，就会造成连接延迟。

解决方案

在 MySQL 配置文件中添加了skip-name-resolve参数，该参数的作用是：

  1. 禁用MySQL的DNS主机名解析功能
  2. 阻止MySQL对客户端IP地址进行反向DNS查询
  3. 消除因DNS解析超时导致的连接延迟

具体操作步骤：

  1. 在/etc/mysql/mysql.conf.d/mysqld.cnf配置文件中添加skip-name-resolve选项
  2. 重启MySQL服务使配置生效 sudo systemctl restart mysqld