时间戳

首页 > 解决方案 > MySQL 插入时出现错误 1022。怎么修？

mysql - MySQL 插入时出现错误 1022。怎么修？

问题描述

我的 MariaDB 数据库有一个非常奇怪的问题。我将它与gorm配对使用 当我尝试向其中一个表发出插入请求时，我收到错误

错误 1022：无法写入；表“标题”中的重复键

好的。我尝试直接从 Navicat 或 Datagrip 和 MAGIC 使用相同的代码 - 一切正常并插入记录。我检查了外键 - 都有唯一的名称，但我仍然不知道如何建模。

表的定义

/*
Navicat MariaDB Data Transfer

Source Server         : My Hetzner
Source Server Version : 100316
Source Host           : 127.0.0.1:3306
Source Database       : sovet_api

Target Server Type    : MariaDB
Target Server Version : 100316
File Encoding         : 65001

Date: 2020-05-09 18:46:52
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for titles
-- ----------------------------
DROP TABLE IF EXISTS `titles`;
CREATE TABLE `titles` (
  `anime_id` int(11) NOT NULL,
  `anime_name` varchar(255) DEFAULT NULL,
  `anime_name_rus` varchar(255) DEFAULT NULL,
  `ai_help` varchar(255) DEFAULT NULL,
  `pidor_ban` int(11) DEFAULT 0,
  `vk_new_template_sub` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `vk_new_template_dub` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `video_name_template_sub` varchar(255) DEFAULT '',
  `video_name_template_dub` varchar(255) DEFAULT '',
  `video_desc_template_sub` text DEFAULT NULL,
  `video_desc_template_dub` text DEFAULT NULL,
  `telegram_new_template_sub` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `telegram_new_template_dub` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `telegram_sub_group_id` bigint(20) DEFAULT -1001269855704,
  `telegram_dub_group_id` bigint(20) DEFAULT -1001269855704,
  `vk_banned` int(11) DEFAULT 0,
  `command_sub_id` int(11) DEFAULT 1,
  `command_dub_id` int(11) DEFAULT 1,
  `default_preroll_id_sub` int(11) DEFAULT NULL,
  `default_watermark_id_sub` int(11) DEFAULT NULL,
  `default_preroll_id_dub` int(11) DEFAULT NULL,
  `default_watermark_id_dub` int(11) DEFAULT NULL,
  `vk_sub_group_id` bigint(20) DEFAULT 33905270,
  `vk_dub_group_id` bigint(20) DEFAULT 33905270,
  `vk_album_id_sub` int(11) DEFAULT NULL,
  `vk_album_id_dub` int(11) DEFAULT NULL,
  `repost_sub_vk_group_id` int(11) DEFAULT 0,
  `repost_dub_vk_group_id` int(11) DEFAULT 0,
  `default_sub_add_att` text DEFAULT NULL,
  `default_dub_add_att` text DEFAULT NULL,
  `gdrive_parent_id` varchar(255) DEFAULT NULL,
  `gdrive_torrent_id` varchar(255) DEFAULT NULL,
  `gdrive_sub_id` varchar(255) DEFAULT NULL,
  `gdrive_font_id` varchar(255) DEFAULT NULL,
  `ai_search_enabled` int(11) DEFAULT 1,
  `nyaa_scan_enabled` int(11) DEFAULT 0,
  `anime_shiki_id` int(11) DEFAULT NULL,
  `anime_name_jpn` varchar(255) DEFAULT NULL,
  `anime_rating` varchar(255) DEFAULT NULL,
  `anime_episodes` int(255) DEFAULT 0,
  `hashtags` text DEFAULT NULL,
  `subscribe_sub_enabled` smallint(6) DEFAULT 0,
  `subscribe_dub_enabled` smallint(6) DEFAULT 0,
  `subscribe_priority` smallint(6) DEFAULT 0,
  `sub_purse` bigint(255) DEFAULT NULL,
  `dub_purse` bigint(255) DEFAULT NULL,
  `sub_qiwi` varchar(255) DEFAULT NULL,
  `dub_qiwi` varchar(255) DEFAULT NULL,
  `sub_paypal` varchar(255) DEFAULT NULL,
  `dub_paypal` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`anime_id`),
  KEY `command_sub_id` (`command_sub_id`),
  KEY `command_dub_id` (`command_dub_id`),
  KEY `default_watermark_id_sub` (`default_watermark_id_sub`),
  KEY `default_watermark_id_dub` (`default_watermark_id_dub`),
  KEY `default_preroll_id_sub` (`default_preroll_id_sub`),
  KEY `default_preroll_id_dub` (`default_preroll_id_dub`),
  FULLTEXT KEY `IDX__titles__anime_name_search` (`anime_name`,`ai_help`),
  FULLTEXT KEY `IDX__titles__anime_name_search_bot` (`anime_name`,`anime_name_rus`,`ai_help`),
  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`command_sub_id`) REFERENCES `command` (`command_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `titles_ibfk_2` FOREIGN KEY (`command_dub_id`) REFERENCES `command` (`command_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `titles_ibfk_3` FOREIGN KEY (`default_watermark_id_sub`) REFERENCES `watermarks` (`watermark_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `titles_ibfk_4` FOREIGN KEY (`default_watermark_id_dub`) REFERENCES `watermarks` (`watermark_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `titles_ibfk_5` FOREIGN KEY (`default_preroll_id_sub`) REFERENCES `prerolls` (`preroll_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `titles_ibfk_6` FOREIGN KEY (`default_preroll_id_dub`) REFERENCES `prerolls` (`preroll_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

和

DROP TRIGGER IF EXISTS `trigger_hash_gen`;
DELIMITER ;;
CREATE TRIGGER `trigger_hash_gen` AFTER INSERT ON `titles`
FOR EACH ROW
    IF NOT EXISTS (SELECT * FROM title_hashtags
         WHERE hashtag_value =    TRIM(BOTH '_' FROM
             REGEXP_REPLACE(NEW.anime_name, '[^a-zA-Z0-9]+','_')))
    THEN
        INSERT INTO title_hashtags(anime_id, hashtag_value)
            VALUES (NEW.anime_id, TRIM(BOTH '_' FROM
             REGEXP_REPLACE(NEW.anime_name, '[^a-zA-Z0-9]+','_')));
END IF
;;
DELIMITER ;

SQL代码

INSERT INTO `titles` (`anime_id`,`anime_name`,`anime_name_rus`,`pidor_ban`,`vk_new_template_sub`,`vk_new_template_dub`,`video_name_template_sub`,`video_name_template_dub`,`video_desc_template_sub`,`video_desc_template_dub`,`telegram_new_template_sub`,`telegram_new_template_dub`,`telegram_sub_group_id`,`telegram_dub_group_id`,`vk_banned`,`vk_sub_group_id`,`vk_dub_group_id`,`vk_album_id_sub`,`vk_album_id_dub`,`default_sub_add_att`,`default_dub_add_att`,`gdrive_parent_id`,`gdrive_torrent_id`,`gdrive_sub_id`,`gdrive_font_id`,`anime_shiki_id`,`anime_name_jpn`,`anime_rating`,`anime_episodes`,`subscribe_sub_enabled`,`subscribe_dub_enabled`,`subscribe_priority`,`sub_purse`,`dub_purse`,`sub_qiwi`,`dub_qiwi`,`sub_paypal`,`dub_paypal`,`ai_search_enabled`,`nyaa_scan_enabled`)
    VALUES ('1046','Urashimasakatasen no Nichijou Special','Деньки Урасимасакатасэн: Школьная пора — Эпизод 13','0','','','','','','','','','0','0','0','0','0','56053731','56053731','','','1-SHGDPX_ybF0Kqu0rtLKcnY_rmfsXa96','1WmRjv8fsZAAY2uBGoRx76Ar1B8rJGKcj','1TvlCnAQ6DbLR3WWiePDCJerYNEwX09oW','1nZRP4Yf92DyuyeOPtW44_vzZLENN6gs6','40921','','','0','0','0','0','0','0','','','','','0','0')

但是当我尝试在 datagrip、navicat 或其他 smth 中使用相同的 SQL 时 - 它可以工作

谢谢！

标签： mysqlinsertmariadb

解决方案

不做怎么样IF...SELECT; 干脆做INSERT IGNORE ...。

或者可能INSERT ... ON DUPLICATE KEY UPDATE ...

推荐阅读