mysql 拾遗
db

mysql 拾遗

技术
更新于 2024-07-27
— 4303 字
返回

联合查询 join

the main difference between JOIN and LEFT JOIN lies in how they retrieve data from tables:

  • JOIN (INNER JOIN): Returns rows when there is a match in both tables. Excludes rows where there is no match between the tables.
  • LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, even if there are no matches in the right table.

示例

假设有两个表：Employees 和 Departments。

Employees 表：

sql
+-----------+----------+------------+
| EmployeeID | Name     | DepartmentID |
+-----------+----------+------------+
| 1         | Alice    | 1          |
| 2         | Bob      | 2          |
| 3         | Charlie  | 3          |
+-----------+----------+------------+

Departments 表：

sql
+------------+------------+
| DepartmentID | DepartmentName |
+------------+------------+
| 1          | IT         |
| 2          | HR         |
| 4          | Marketing  |
+------------+------------+

INNER JOIN

使用 INNER JOIN 根据 DepartmentID 字段将 Employees 表和 Departments 表连接起来：

sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

结果集：

sql
+----------+----------------+
| Name     | DepartmentName |
+----------+----------------+
| Alice    | IT            |
| Bob      | HR            |
+----------+----------------+

OUTER JOIN 示例

使用 LEFT OUTER JOIN 来展示所有 Employees 以及他们对应的部门，即使某些员工没有分配部门：

sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

结果集：

sql
+----------+----------------+
| Name     | DepartmentName |
+----------+----------------+
| Alice    | IT            |
| Bob      | HR            |
| Charlie  | NULL          |
+----------+----------------+

在这个结果集中，Charlie 没有对应的部门，因此在 DepartmentName 列中显示为 NULL。

OUTER JOIN 还包括 RIGHT OUTER JOIN 和 FULL OUTER JOIN，但使用频率较低。FULL OUTER JOIN 会返回两个表中所有行的组合，如果一个表中有匹配，另一个表中没有，则没有匹配的列显示为 NULL。

arm64 debian 安装 mysql

使用 default-mysql-server

https://serverfault.com/questions/379714/unknown-unsupported-storage-engine-innodb-mysql-ubuntu

二进制文件大小

sql
SELECT id, LENGTH(data) AS size FROM binary_data WHERE id = 1;

数据库备份&导入

导出以当前日期结尾的 .sql 文件：

  • 为了防止中文乱码，需要使用 --result-file 这个参数来指定导出文件，而不是使用 >
shell
mysqldump --host localhost --port 3306 --user username -p   --default-character-set=utf8 database_name --result-file="cheer_backup_$(date +'%Y-%m-%d').sql"

导入：

shell
mysql --host localhost --port 3306 -u username -p database_name < file.sql

truncate 和 drop

操作描述是否删除数据是否删除表结构是否触发触发器是否记录事务日志
TRUNCATE TABLE删除表中的所有数据，但保留表的结构和定义是否否否
DROP TABLE完全删除表，包括表的结构、定义和数据是是是是

连接时区

可以使用以下方式来设置 MySQL 连接时区为中国时区：

1.在连接 MySQL 数据库时，向 MySQL 发送“SET time_zone”语句来设置会话时区：

sql
SET time_zone = '+8:00'; -- 设置为中国时区

2.在 MySQL 配置文件中设置默认时区为中国时区：编辑 MySQL 配置文件（一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf），添加或修改以下行：保存文件并重启 MySQL 服务，即可使 MySQL 默认时区设置为中国时区。

[mysqld]
default-time-zone = '+8:00'

在使用 MySQL 连接字符串连接数据库时，默认时区将与 MySQL 服务器的时区匹配。如果已经在 MySQL 配置文件中设置默认时区，连接字符串中无需再次设置时区。

如果您需要在连接字符串中设置 MySQL 会话时区：添加 “?serverTimezone=Asia/Shanghai” 参数，值为您需要的时区。如果您不想在连接字符串中设置时区，可以选择在连接 MySQL 数据库后，使用 “SET time_zone” 语句来设置当前会话时区。

使用 toLocaleString() 得到的时间，是根据客户端机器本地时区来格式化输出的。假设，您的服务器和数据库都设置的是 Asia/Shanghai 时区，但是您的客户端机器所在的时区为 UTC+8，则 toLocaleString() 得到的时间就会快 8 个小时。

为了解决这个问题，您可以通过以下两种方式：

1.在控制台中设置客户端机器的本地时区

您可以在浏览器控制台中设置客户端机器的本地时区，来获得正确的输出结果。例如，在控制台中执行以下代码：

Intl.DateTimeFormat().resolvedOptions().timeZone = "Asia/Shanghai"

这将设置客户端机器的本地时区为 Asia/Shanghai，但这种方法并不是很实用，因为大多数用户不知道该如何在控制台中执行这些代码。

2.在服务端返回正确的时区时间

另一种方法是，在服务端将时间转换为客户端机器所在时区的时间。您可以使用 Moment.js 等工具库，在服务端将时间按照客户端机器本地时区进行转换。例如，您可以使用以下代码将数据库中存储的时间转换为客户端机器本地时区的时间：使用 Moment.js 库的 tz() 方法将数据库中的时间转换为 Asia/Shanghai 时区的时间，并使用 format() 方法将时间按照 ISO 8601 格式输出。在客户端使用 toLocaleString() 方法输出当前本地时间时，就可以得到正确的结果了。

javascript
const moment = require('moment-timezone');
// 将数据库中的时间按照 'Asia/Shanghai' 时区转换为客户端本地时区的时间
const localTime = moment.utc(databaseTime).tz('Asia/Shanghai').format();
// 将转换后的本地时间返回给客户端
res.send(localTime);

如果您已经将 MySQL 的时区设置为 UTC+0，并且插入时间数据时也是按照 UTC+0 的时间插入的，那么如果您要将这些时间数据统一修改为 UTC+8 时区的时间，可以执行以下 SQL 语句：使用 MySQL 内置函数 CONVERT_TZ() 将原来存储在 UTC+0 时区的时间转换为 UTC+8 时区的时间。

sql
UPDATE your_table SET your_datetime_column = CONVERT_TZ(your_datetime_column, '+00:00', '+08:00');