主页 / user-199011

Maurice's questions

Martin Hope
Maurice
Asked: 2022-10-14 12:05:13 +0800 CST
  • 5

MySql 工作台将密码存储在一个名为的文件user_data.dat中，默认情况下该文件是加密的。但是这种加密有多强，究竟使用了什么算法呢？网上有一些工具可让您解密user_data.dat文件以恢复忘记的密码。这让我想知道加密究竟有多强，以及密码库是否足够安全以存储生产数据库的密码。

mysql-workbench
Martin Hope
Maurice
Asked: 2020-05-10 05:19:01 +0800 CST
  • 0

我有一个被调用的函数GET_TRANSLATION，它可以根据提供的枚举值基于不同的选择查询返回不同的结果。这是完整的代码：

CREATE FUNCTION GET_TRANSLATION(preferredLanguageId BIGINT, backupLanguageId BIGINT, entityId BIGINT, translationSubject ENUM('CROP_NAME', 'CROP_VARIETY_NAME', 'JOURNAL_TITLE', 'SOIL_TYPE', 'COUNTRY', 'PROVINCE', 'PLACE'))
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
    CASE
        WHEN translationSubject = 'CROP_NAME' THEN
            RETURN (SELECT COALESCE((SELECT s.translatedName FROM CropNameTranslation s WHERE s.language_id = preferredLanguageId AND s.parent_id = entityId AND s.approved = TRUE LIMIT 1),
            (SELECT s.translatedName FROM CropNameTranslation s WHERE s.language_id = backupLanguageId AND s.parent_id = entityId AND s.approved = TRUE LIMIT 1), (SELECT s.translatedName FROM CropNameTranslation s WHERE s.parent_id = entityId AND s.approved = TRUE LIMIT 1)));
        WHEN translationSubject = 'CROP_VARIETY_NAME' THEN
            RETURN (SELECT COALESCE((SELECT s.translatedName FROM CropVarietyName s WHERE s.language_id = preferredLanguageId AND s.parent_id = entityId AND s.approved = TRUE LIMIT 1),
            (SELECT s.translatedName FROM CropVarietyName s WHERE s.language_id = backupLanguageId AND s.parent_id = entityId AND s.approved = TRUE LIMIT 1), (SELECT s.translatedName FROM CropVarietyName s WHERE s.parent_id = entityId AND s.approved = TRUE LIMIT 1)));
        WHEN translationSubject = 'JOURNAL_TITLE' THEN
            RETURN (SELECT COALESCE((SELECT s.title FROM JournalTitleAndContent s WHERE s.language_id = preferredLanguageId AND s.journal_id = entityId LIMIT 1),
            (SELECT s.title FROM JournalTitleAndContent s WHERE s.language_id = backupLanguageId AND s.journal_id = entityId LIMIT 1), (SELECT s.title FROM JournalTitleAndContent s WHERE s.journal_id = entityId LIMIT 1)));
        WHEN translationSubject = 'SOIL_TYPE' THEN
            RETURN (SELECT COALESCE((SELECT s.translatedName FROM SoilTypeNameTranslation s WHERE s.language_id = preferredLanguageId AND s.soiltypedetails_id = entityId LIMIT 1),
            (SELECT s.translatedName FROM SoilTypeNameTranslation s WHERE s.language_id = backupLanguageId AND s.soiltypedetails_id = entityId LIMIT 1), (SELECT s.translatedName FROM SoilTypeNameTranslation s WHERE s.soiltypedetails_id = entityId LIMIT 1)));
        WHEN translationSubject = 'COUNTRY' THEN
            RETURN (SELECT COALESCE((SELECT ct.translatedName FROM countryNameTranslation ct WHERE ct.parent_id = entityId AND ct.language_id = preferredLanguageId LIMIT 1),
            (SELECT ct.translatedName FROM countryNameTranslation ct WHERE ct.parent_id = entityId AND ct.language_id = backupLanguageId LIMIT 1), (SELECT ct.translatedName FROM countryNameTranslation ct WHERE ct.parent_id = entityId LIMIT 1)));
        WHEN translationSubject = 'PROVINCE' THEN
            RETURN (SELECT COALESCE((SELECT ct.translatedName FROM provinceNameTranslation ct WHERE ct.parent_id = entityId AND ct.language_id = preferredLanguageId LIMIT 1),
            (SELECT ct.translatedName FROM provinceNameTranslation ct WHERE ct.parent_id = entityId AND ct.language_id = backupLanguageId LIMIT 1), (SELECT ct.translatedName FROM provinceNameTranslation ct WHERE ct.parent_id = entityId LIMIT 1)));
        WHEN translationSubject = 'PLACE' THEN
            RETURN (SELECT COALESCE((SELECT ct.translatedName FROM placeNameTranslation ct WHERE ct.parent_id = entityId AND ct.language_id = preferredLanguageId LIMIT 1),
            (SELECT ct.translatedName FROM placeNameTranslation ct WHERE ct.parent_id = entityId AND ct.language_id = backupLanguageId LIMIT 1), (SELECT ct.translatedName FROM placeNameTranslation ct WHERE ct.parent_id = entityId LIMIT 1)));
        ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid enumType!';
    END CASE;
END

我在一个更大的选择查询中使用了这个函数。IESELECT c.scientificName, GET_TRANSLATION(@languageId, 1, j.soiltype_details_id, 'CROP') as soilType FROM journal j;

我错误地给它输入CROP而不是CROP_NAME. 现在它应该已经进入结构的else一部分CASE并返回错误消息invalid enumType。但是，我真正得到的是错误Error Code: 1265. Data truncated for column 'translationSubject' at row 1。

我的问题是；为什么它不触发我为无效枚举类型定义的错误，为什么它被translationSubject视为“截断”的列？这个错误完全不合适，因为原因仅仅是一个错误的枚举值。

谁能向我解释这种行为？

谢谢

编辑：

我只是尝试将 SIGNAL 更改为 RESIGNAL 并删除 SQLSTATE 参数。此外，我添加了

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        RESIGNAL;
    END;

到 GET_TRANSLATION 但同样的错误仍然存​​在。

mysql-8.0
Martin Hope
Maurice
Asked: 2020-03-04 03:09:38 +0800 CST
  • 0

我做了这个查询：

    SELECT 
        COALESCE(
                 (SELECT * 
                  FROM JournalEntryTitleAndContent jetc 
                  WHERE jetc.language_id = 2 
                        AND jetc.journalentry_id = 1 LIMIT 1),
                 (SELECT * 
                  FROM JournalEntryTitleAndContent jetc 
                  WHERE jetc.language_id = 1 
                        AND jetc.journalentry_id = 1 LIMIT 1),
                 (SELECT * 
                  FROM JournalEntryTitleAndContent jetc 
                  WHERE jetc.journalentry_id = 1 LIMIT 1)
                 )

其目的是检查是否存在具有 language_id 2 的行（那是首选语言）。如果不存在，则合并应尝试查找具有 id 1 的 fallbacklanguage 的行，如果它也返回 null，那么它应该返回它可以找到的第一行 journalentry_id = 1。

当我在工作台中执行此语句时，我得到Error Code 1241: Operand should contain 1 column(s)

我不能从合并中返回一行作为单个值吗？如果不是，我应该用什么代替呢？我应该使用CASE还是IFNULL()代替？

谢谢

mysql coalesce