这更像是一个“请确认/纠正我的理解”的问题。

背景 我使用 JDBC 连接到 MySQL，连接池有大约 250 个连接，其中大多数连接即使在查询终止后也似乎持续连接 - 全部使用相同的用户帐户。我希望使用临时表的存储过程由 Java 代码调用，并对 JSON 列执行转换，将其转换为不同表的行。该转换相对昂贵，可能会在数千行上执行，因此我不希望多次执行该转换。为了解决这个问题，我建议的解决方案是将数据转换为临时表，围绕现在转换的数据执行统计查询，然后将其批量插入到目标表中。

问题 我读过临时表绑定到User。我的理论是否正确，因为连接似乎从未终止并且都使用相同的用户帐户，所以在过程中创建的临时表将在所有连接和查询之间共享（因此数据可以由运行该连接的其他连接访问）同时执行相同的程序）？

限制 我想要做的是要么传输原始数据，要么执行转换并将该数据传输回 Java。然而，我们每次处理大约几百兆字节的数据，并且数据传输回 Java 似乎会锁定数据库，直到完成传输。（我不确定为什么，如果有人有解决这个问题的建议，请告诉我）

因此，我唯一能想到的其他解决方案是不传输源数据，而是执行转换，收集我需要的聚合数据，然后将转换后的数据插入到目标，而无需离开数据库服务器。

提前致谢

我的 MySQL 服务器已正确配置以下参数

mysql> show global variables like '%have_ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'ssl_ca';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| ssl_ca        | ca.pem |
+---------------+--------+
1 row in set (0.00 sec)

mysql> show global variables like 'ssl_cert';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| ssl_cert      | server-cert.pem |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> show global variables like 'ssl_key';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| ssl_key       | server-key.pem |
+---------------+----------------+
1 row in set (0.00 sec)
mysql> show global variables like 'datadir';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| datadir       | /dados/ |
+---------------+---------+
1 row in set (0.00 sec)

这些文件存在于datadir

[root@mysqlen1 dados]# ls -l ca.pem server-cert.pem server-key.pem
-rw-r--r-- 1 mysql mysql 1112 Nov  3 10:28 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Nov  3 10:28 server-cert.pem
-rw------- 1 mysql mysql 1680 Nov  3 10:28 server-key.pem

然后我创建一个用户：

mysql> create user 'teste'@'%' identified by 'teste123' require ssl;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

我可以使用证书进行连接

[root@TCCPUC-ENGDADOS:~]# mysql -uteste -p'teste123' -h 192.168.0.110 --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

但也没有证书

[root@TCCPUC-ENGDADOS:~]# mysql -uteste -p'teste123' -h 192.168.0.110
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

它是否正确？我可以强制用户仅使用这些证书登录吗？

我需要优化具有大量字符串（例如Z1719121.

我的表结构：

codfabrica  => varchar 191 => nullable
codoriginal => varchar 191 => nullable
aplicacao   => mediumText  => nullable

表索引：

codfabrica  => non_unique 1 | seq_in_index 1 | collation A    | cardinality 42466
codoriginal => non_unique 1 | seq_in_index 1 | collation A    | cardinality 7449
aplicacao   => non_unique 1 | seq_in_index 1 | collation NULL | cardinality 42550

laravel 查询代码：

$allKeys = [...]; // array with lots of strings
$q = MyModel::query();
$q->whereIn('codfabrica', $allKeys);
$q->orWhereIn('codoriginal', $allKeys);
$q->orWhereRaw('MATCH (aplicacao) AGAINST (?)', array($allKeys));

结果：

array with 1000 strings => took 5 seconds
array with 2500 strings => took 35 seconds

有什么方法可以优化这个搜索吗？

我对 INDEX 没有经验，那么我是否以正确的方式创建了索引？

该表当前有42.000 rows和20mb total size，托管在AWS db.t3.smallRDS 服务器（2 个 vCPU、2 GiB 内存）上。

我刚刚升级到mysql8

已为phpmyadmin创建了新用户，但该用户无法创建新数据库？

在 phpmyadmin 中

No privileges to create databases

创建用户

CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'xxx'; FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost'; FLUSH PRIVILEGES;

更新

SHOW GRANTS FOR 'phpmyadmin'@'localhost';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `phpmyadmin`@`localhost`
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `phpmyadmin`@`localhost`

..

SELECT USER(), CURRENT_USER();

phpmyadmin@localhost
phpmyadmin@localhost

我有脚本来加密表中的列。>200k 行。在 100k 上它的工作。

UPDATE table
SET col1 = TO_BASE64(AES_ENCRYPT(col1, @keyBase, @static_iv)), 
col2 = TO_BASE64(AES_ENCRYPT(col2 , @keyBase, @static_iv))

编辑：我在更简单的示例（>200k 记录）上重现了这一点：

UPDATE table
SET col1 = '1'

我在MySQL中使用root用户登录，并且服务器已经加固，所以设置了这个参数default-authentication-plugin=caching_sha2_password，并且这是我目前唯一的用户。

出现以下错误： ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

我有一列包含以下格式的字符串值AB12345678：两个字母后跟 8 个数字。

5我需要编写一个 MySQL 查询来在每组 4 位数字变为 之前AB12345678插入a AB5123455678。

我很接近，以下内容适用于列中的一个值，但不适用于多个值 - 所有值都使用看起来相同的第一个值写入。

SELECT @twoeight := os_grid_ref FROM projects WHERE os_grid_ref REGEXP '[A-Z]{2}[0-9]{4}[0-9]{4}';
SELECT @prefix1 := SUBSTR(@twoeight,1,2);
SELECT @part1 := SUBSTR(@twoeight,3,4);
SELECT @part2 := SUBSTR(@twoeight,7,4);
UPDATE projects SET os_grid_ref=CONCAT(@prefix1,'5',@part1,'5',@part2) WHERE os_grid_ref REGEXP '[A-Z]{2}[0-9]{4}[0-9]{4}';

我需要调整什么，以便查询可以单独修改列中的每个值。

谢谢。

在 MySQL 8.0.33 上，我遇到了一种文档未解释的争用形式（在多个实例上重现）。

例如：

1. 我启动一个实例
2. 在控制台客户端上，我在一个相对较大的表上调用 a SELECT COUNT(*)（几 GB 的总记录+索引）
3. ANALYZE TABLE在一个单独的客户端上，我在一个单独的、较小的表上调用

结果是第二条语句（ANALYZE TABLE）挂起一段时间，处于阻塞状态System Lock；这是一个示例输出：

+----+--------+------+-------+---------+------+-------------+---------------------------------+
| Id | User   | Host | db    | Command | Time | State       | Info                            |
+----+--------+------+-------+---------+------+-------------+---------------------------------+
| 18 | _user_ | _ip_ | _db_  | Query   |   72 | executing   | SELECT COUNT(*) FROM _table_1_  |
| 32 | _user_ | _ip_ | _db_  | Query   |   45 | System lock | ANALYZE TABLE _table_2_         |
+----+--------+------+-------+---------+------+-------------+---------------------------------+

根据文档（此处和此处），MySQL 似乎在对第一个 ( ) 表的记录进行分页时锁定（至少）两个表SELECT。

究竟是什么导致 MySQL 在对一个表执行 SELECT 时锁定多个表？

我试图了解percona如何进行热备份的过程。据了解，他们使用innodb崩溃恢复机制并执行以下步骤。

1. 存储/记住重做日志中的起始LSN
2. 复制数据文件
3. 执行 LOCK BINLOG FOR BACKUP：阻止可能更改二进制日志位置的所有操作
4. 复制 REDO 日志文件并获取二进制日志坐标
5. 完成的xtrabackup将解锁二进制日志和表。

我想从重做日志中了解LSN的作用是什么，我们不能直接检查步骤1中的二进制日志位置吗？

那么最终的LSN呢，它在流程中的任何地方都没有使用吗？

假设我们有以下数据库：

或者伪sql格式（所有列都不为空）：

create table road (
  id int PK
) 
create table item_on_road (
  id int PK
  road_id int FK road.id
  position_order int
  UNIQUE (road_id, position_order)
)
create table rock (
  id int PK FK item_on_road.id
  size float
)
// and so on

我们有一辆road可以朝一个方向行驶的车。

沿着这条路，我们可以找到不同的东西，item_on_road以便我们遇到它们，position_order例如1,2,3

我们在路上发现的东西可能会有所不同，从sign到gas_station。

现在我想问一个问题：

1. 这个数据库设计是否符合db 1-5th范式？我认为不会，因为我们可以item_on_road在没有相应的情况下创建条目rock

2. 如果它不符合第五范式，该怎么办？

可选问题：

3. 如果1个问题的答案是肯定的，如何确保rockif item_on_roadcreate的存在？

4. 如果 1 个问题的答案是肯定的，如何确保我们无法创建rock并sign指向相同的 id？