DevelJoe's questions

我正在使用 MariaDB 10.3，而我想要做的是建立一对多的关系，例如在“一对多关系”部分中指定的示例。我正在尝试通过以下方式这样做：

CREATE TABLE test_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  test_value INT NOT NULL DEFAULT ID
);

CREATE TABLE related_table (
  ID INT NOT NULL,
  message VARCHAR(10) NOT NULL DEFAULT 'hello',
  FOREIGN KEY (ID) REFERENCES test_table(test_value)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
)

SQL Fiddle Editor 等在执行时失败，错误：

Function or expression 'AUTO_INCREMENT' cannot be used in the DEFAULT clause of 'ID'

所以我想你不能将一列的默认值设置为等于另一列，如果另一列的值是由函数或表达式定义的？

如果我然后省略AUTO_INCREMENT，我会得到：

Can't create table "fiddle"."related_table" (errno: 150 "Foreign key constraint is incorrectly formed")

所以我假设您不能基于具有默认值的引用列创建 FOREIGN KEY 约束？

您是否看到另一种可能性来做我需要的事情，那就是：

A）当我插入一条新记录test_table但不提供test_value时，它的值应该等于插入记录的主键，这应该是自动生成的。

B) related_table'sID应该将test_table'test_value称为外键。

我目前看到的唯一解决方法是：

CREATE TABLE test_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  test_value INT NOT NULL
);

CREATE TABLE related_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  message VARCHAR(10) NOT NULL DEFAULT 'hello',
  FOREIGN KEY (ID) REFERENCES test_table(test_value)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
);

然后，如果我将例如 3 条记录插入test_table：

1. 将第一条新记录插入test_table，然后获取LAST_INSERT_ID.
2. test_value通过将 的值设置为等于返回的值来更新刚刚创建的第一条记录LAST_INSERT_ID。
3. 将剩下的两条记录插入到test_table中，这次直接将LAST_INSERT_ID之前返回的作为 的值test_value。
4. related_table将返回的值LAST_INSERT_ID作为其主键ID字段插入新记录。

但这似乎过于复杂。这个用例没有更好的解决方案吗？

我决定从基于 PHP 的本地会话管理切换$_SESSION到使用我自己的会话管理，因为到目前为止我在 PHP 的可能性方面遇到了许多不同的问题，也因为我正在构建一个必须与移动应用程序请求兼容的 API ; $_SESSION所以无论如何我都不能依赖。我知道有 symfony 之类的框架可以解决这个问题，但是对这些婴儿操作使用这样的东西就有点过分了。

因此，我目前正在实施的是根据这个逻辑（来源）：

我想知道的最后一点是，Redis / 链接的文章建议在第一次成功的身份验证（如登录或类似身份）之后“存储会话和用户信息”。但是，我认为不这样做会更有益，并且只存储会话标识符，有点像允许客户端在 Web 应用程序的安全区域中启动请求的 VIP 卡。

我的想法就是这样：

• 无论如何，大多数相关的用户信息都以未加密的形式存在于数据库中；大多数甚至在单个数据库表中。
• 我认为将相同的数据存储在数据库的多个位置通常是一种不好的做法，即使在这种意义上存储一个数据是为了“更快”或“促进”访问。
• 整个用户信息 （=平台所有可能的 REST 请求所需的所有数据，收集在一个 JSON 中）涵盖大约 50 个键值对，其中一些值是加密的，所以我还需要加密生成的用户info，导致对每个请求（和页面加载，也用于身份验证）进行巨大的加密/解密操作，以及相应的附加列，例如VARBINARY(600).
• 对于一个请求，我从不需要超过 10 个 kv 数据，而且大多只需要 2-3 个。

考虑到上述考虑，我想知道：沿着会话标识符存储整个用户信息（我需要在平台的所有 REST 请求中的所有用户数据的总和）来模拟，这不是完全矫枉过正和大量数据传输吗？一个会议？仅将会话标识符存储在数据库中，并在实际请求进入时仅查询所需的用户信息不是更好吗（上面 img 的步骤 4a）？这不是更有意义吗？

如果我将整个用户信息与会话标识符一起存储，我只是觉得浪费了这么多资源。因为这也意味着我会为不同的请求解密和发送大量的有效载荷，但这是徒劳的。

那么这里有什么更好的做法，在身份验证时存储用户信息（步骤 1b）或在对请求进行身份验证时检索它（步骤 4b）？

给你一个想法；由此产生的权衡需要在以下之间进行：

• 每次请求都解密数据并发送比端点实际需要的更多的用户信息

VS

• 平均而言，在每次页面加载/请求时对主键执行至少 2 次联接，最终在该查询中也进行 1-2 次较小的解密。

在性能方面，我对这里更聪明的东西一无所知..

另一个权衡是编码的便利性，因为不将整个会话数据集存储在数据库中意味着每个 REST 调用都有一个特定的用户信息数据库请求。

好吧，我有一个 MariaDB 数据库，我刚刚注意到一些奇怪的存储问题，如下所示：

• 我的JSON列（在 MariaDB 中是LONGTEXT）有一个自动设置的排序规则utf8mb4_bin。我刚刚注意到这实际上弄乱了我所有的撇号，将它们存储为'，也存储é为\u00e9等。

• 其他一些列包含法语/西班牙语/葡萄牙语等字母，并且é确实é显示。它使用的排序规则是utf8mb4_unicode_ci.

• 另一列包含可能包含 HTML 标记的字符串，utf8mb4_unicode_ci并且标记没有任何问题。但是，由于某种原因，它转义了单引号，因此它存储'为\'，必须避免。

所以我的问题是，在 MariaDB 中，您最好使用哪种排序规则来避免在存储 HTML 标记、特殊字符如 é、ö、ä、è 以及不转义撇号时遇到任何问题？

更新

实际上，我真的不明白为什么会这样：

• 在我的示例数据库表中，我有两列，A 和 B。表的字符集是utf8mb4，并且没有一列有专门分配的字符集。

• 该表使用默认排序规则utf8mb4_unicode_ci

• A 列使用排序规则utf8mb4_unicode_ci

• B 列使用了排序规则utf8mb4_bin

• A 列正确地将字母存储为é、ä等。

• B 列使用它们的 unicode 存储它们，例如\u00e9for é。

我现在使用以下方法更改了 B 列的排序规则：

ALTER TABLE sample_table MODIFY COLUMN column_b LONGTEXT COLLATE utf8mb4_unicode_ci.

因此，A 列和 B 列现在使用完全相同的字符集 + 排序规则。插入column_b的数据始终是JSON_OBJECT.

尽管如此，无论更改如何，仅在 B 列中而不是在 A 列中，字母 likeé仍以其 unicode 编码格式存储，撇号也存储为'. 查询数据时，任何特殊字母 like 都会é被正确检索（如果您查询数据持有 sth like l\u00e9ger，则正确得到léger）。

但是，如果你查询 sth like l'\u00e9l\u00e9phant，你不会得到l'éléphant, 但是l'éléphant。

我在 PHP 中获取数据，并确保连接的字符集也是utf8mb4, 使用mysqli_set_charset( $connection, 'utf8mb4' );.

我知道理论上我可以简单地对检索到的数据进行搜索和替换；但是为什么这种撇号转换还在发生呢？

更新 2

找到了'问题的解决方案（请参阅我发布的答案），但现在我正试图找出解决另一个提到的问题的方法：当存储"Hello, I'm James"在LONGTEXT数据字段中时，MariaDB 存储Hello, I\'m James（它转义了其中的单引号，猜出于安全原因）。目前，当我检索数据时，我得到

Hello, I\'m James

但我想得到

Hello, I'm James

即使数据存储为

Hello, I\'m James

当然，您可以再次在 PHP 中进行搜索替换，但我只是觉得 MariaDB 中必须有一个标准方法，在数据库方面，为此..？

更新 3

感谢@Rick James 的提示，在这种情况下，在数据库服务器端不应该发生数据字符集转换，我一直在检查我的服务器端代码，确实发现了关于 unicode 字符问题的第一个问题（\u00e9而不是é, ETC。）。原因是，在将 JSON 插入我的数据库之前，我已经使用json_encode从 PHP 数组转换为 JSON 字符串。问题是\uXXXX默认情况下这会转义我的 unicode 字符，在这种情况下不应该这样做。为了避免这种情况，而不是：

json_encode( $data )

利用：

json_encode( $data, JSON_UNESCAPED_UNICODE )

这样就解决了与 unicode 编码错误相关的所有问题。关于奇怪的撇号编码到 '/转义到的\'问题仍未解决。

更新 4

好的，也找到了问题的根源'，\'它们是由同一个问题引起的。这是因为我使用以下方法清理了用于插入的字符串数据：

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING
);

代替：

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING,
  FILTER_FLAG_NO_ENCODE_QUOTES
);

当您回显字符串时，这从未显示'编码，可能是因为它在回显时转换回单引号，但这只是一个假设。是的，做同样的事情\'而不是'存储也解决了这个问题。所以我想就是这样。

这是我第一次在 MariaDB 中使用加密，所以我需要确保我做对了。我需要简单地以加密方式存储一些标识符，并且想知道我是否正确地执行了它。由于我在数据检索时遇到了一些意外行为（有时只是），我怀疑有什么问题（而且我一直在检查文档等，这一切似乎都是正确的..？）。

• 我将数据存储在BLOB和NOT NULL列中，没有指定其他内容。
• 我正在使用它插入数据，而key通过openssl rand -base64 32

INSERT INTO data_table ( encr_data ) VALUES( AES_ENCRYPT( "secret_string", "key" ) );

• 我正在使用以下方法检索数据：

SELECT CAST( AES_DECRYPT( encr_data, "key" ) AS CHAR ) as encr_data FROM data_table;

这是在 MariaDB 中正确的做法吗？秘密字符串由大约 35 - 40 个字符组成。

这对我来说真的很重要，因为我正在编写的应用程序正在存储一些秘密数据以与另一个 API 一起使用。换言之，加密本身不应带来任何有关数据完整性的风险。只是想到由于任何加密错误（使加密+存储的数据无法使用）而需要重新生成所有加密数据......

所以我需要确保加密字符串中没有数据被裁剪，并且加密正确完成，因此是这个问题的原因。

尽管这个问题非常相似，但我不认为这也是我案例的最佳方法，尽管它似乎有效。考虑以下示例：

CREATE TABLE messages (
  writer VARCHAR(5),
  recipient VARCHAR(5),
  show_for_writer TINYINT(1), -- 0 for hidden, 1 for display
  show_for_recipient TINYINT(1) -- idem
);

我现在想创建一个更新查询，x为他隐藏用户的所有消息；即show_for_writer在是 的情况下设置等于 0 ，在writer是的情况下x设置show_for_recipient等于 0 。请注意，我希望尽可能高效地完成此操作，因为该表可能包含数百万条记录（每条消息一条），用于无数的作者-收件人组合（复合、非唯一索引）。recipientx

根据链接的帖子，我试图做的是：

UPDATE messages
SET show_for_writer =
    CASE
    WHEN writer = 'x'
    THEN 0
    ELSE show_for_writer -- or even better, do nothing; only update if necessary
    END
    ,
    show_for_recipient =
    CASE WHEN recipient = 'x'
    THEN 0
    ELSE display_receiver -- or even better, do nothing; only update if necessary
    END
WHERE writer = 'x' OR recipient = 'x';

然而，我觉得这根本不理想，因为它会遍历表的所有记录，而且这个查询可能不会以常规规模完成，但也不是很少。什么是最好的解决方案？我愿意接受任何解决方案，甚至是结构柱修改提议；我正在使用 MariaDB 10.3。

我的一个想法可能是按如下方式编辑结构：

CREATE TABLE messages (
 participants VARCHAR(10) FULLTEXT,
 writer VARCHAR(5) NOT NULL,
 recipient VARCHAR(5) NOT NULL,
 show JSON NOT NULL
);

并执行以下操作：

UPDATE messages
SET show = JSON_REPLACE(messages,'$.x',0)
WHERE MATCH(participants) AGAINST('x');

这里的问题是我查询消息是否在初始化时显示为“x”。因此，设置show为JSON数据类型将无法对该列进行索引，因此无法show有效地在 f() 中进行查询。这就是为什么我认为这个想法可能并不理想，只是为了向您展示我基本上对结构层面的修改持开放态度。

否则我可以简单地触发两个后续UPDATE语句，如下所示：

UPDATE messages
SET show_for_writer = 0
WHERE writer = 'x';

UPDATE messages
SET show_for_recipient = 0
WHERE recipient = 'x';

但我真的更喜欢坚持每个操作使用一个查询。请帮忙！

检查这里的示例，更新查询被优化以避免遍历表的所有记录：

http://www.sqlfiddle.com/#!9/f6830d/1

关系型数据库：MariaDB 10.3

假设您有一个表，它根据给定的用户名对存储特定信息（ 、 等）info_1；info_2像这样：

+----------+----------+----------+----------+----------+------+--..
|  user_1  |  user_2  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+----------+----------+----------+----------+----------+------+--..

其中 和 的值等于特定user_1且user_2不同的用户名 ( VARCHAR(50))，并在创建时定义为表的多列索引 (user_1,user_2)。我的应用程序在以下功能中搜索的查询：

A）给定的用户对：

WHERE user_1 = name_1 AND user_2 = name_2 OR user_1 = name_2 AND user_2 = name_1

B）给定用户/返回特定用户是成员之一的所有记录：

WHERE user_1 = name_1 OR user_1 = '%' AND user_2 = name_1

问题：我必须确保搜索总是被索引。但是，使用OR子句时，每次都会执行全表扫描。

解决方案一）

由于这个答案，导致这些案例的索引搜索的可能查询是：

一个）

SELECT * FROM my_table WHERE user_1 = name_1 AND user_2 = name_2
UNION ALL
SELECT * FROM my_table WHERE user_1 = name_2 AND user_2 = name_1;

二）

SELECT * FROM my_table WHERE user_1 = name_1
UNION ALL
SELECT * FROM my_table WHERE user_1 = '%' AND user_2 = name_1;

然而，总是使用两个SELECT语句和一个WHERE子句的缺点总是徒劳的（因为每个用户对总是有一个记录）。这就是为什么我尝试通过使用全文索引来进一步优化查询，结果如下表：

解决方案二）

+---------+----------+----------+----------+------+--..
|  users  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+---------+----------+----------+----------+------+--..

whereusers已被定义为FULLTEXT VARCHAR(150)，其值始终对应于 format name_1-name_2，而我使用连字符是因为在MATCH...AGAINST语法中使用逗号来执行全文索引查询。

有了这个结构，我现在得到了 A) 和 B) 的以下新查询可能性：

一个）

SELECT * FROM my_table WHERE MATCH(users) AGAINST ('"name_1-name_2","name_2-name_1"');

二）

SELECT * FROM my_table WHERE MATCH(users) AGAINST ('name_1');

在我看来，这极大地促进了查询结构，并且既不使用两个语句，也不使用将徒劳SELECT的附加子句。WHERE尽管如此，由于我对查询性能优化以及全文索引搜索与常规索引搜索的比较知之甚少，我想知道 I 和 II 的哪个解决方案可能执行得更好，为什么？还是有更好的方法？