dierre's questions

我需要通过 mysql 脚本创建触发器

CREATE TRIGGER sync_new_oauth_sessions
    AFTER INSERT
    ON oauth_sessions FOR EACH ROW
BEGIN
    SET @s = 'INSERT INTO oauth_sessions_new(id, client_id, owner_type, owner_id, owner_id_new) VALUES (?, ?, ?, ?, CAST(NULLIF(?,'''') AS UNSIGNED))';
    -- INSERT INTO oauth_sessions_new(id, client_id, owner_type, owner_id, owner_id_new) VALUES (NEW.id, NEW.client_id, NEW.owner_type, NEW.owner_id, CAST(NULLIF(NEW.owner_id,'') AS UNSIGNED));
    PREPARE stmt2 FROM @s;
    SET @sid = NEW.id;
    SET @s_client_id = NEW.client_id;
    SET @s_owner_type = NEW.owner_type;
    SET @s_owner_id = NEW.owner_id;
    SET @s_owner_id_new = NEW.owner_id;
    EXECUTE stmt2 USING @sid, @s_client_id, @s_owner_type, @s_owner_id, @s_owner_id_new;
END

这是脚本的内容。我越来越： Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

我不明白我做错了什么。如果我这样做\'\'了，我也会遇到同样的问题。

谢谢

我有下表：

CREATE TABLE `tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `s_id` int(10) unsigned NOT NULL,
  `a_token` char(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `a_token_exp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_a_token` (`a_token`) USING HASH,
  KEY `f_seid` (`s_id`),
  CONSTRAINT `f_seid` FOREIGN KEY (`s_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

我想更改字符集，a_token因为我们使用此命令仅使用 ascii 字符：

ALTER TABLE tokens MODIFY a_token CHAR(40) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL;

和新SHOW CREATE TABLE节目：

CREATE TABLE `tokens` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id` int(10) unsigned NOT NULL,
  `a_token` char(40) CHARACTER SET ascii NOT NULL,
  `a_token_exp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_a_token` (`a_token`) USING HASH,
  KEY `f_seid` (`s_id`),
  CONSTRAINT `f_seid` FOREIGN KEY (`s_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

然后我跑OPTIMIZE TABLE oauth_session_access_tokens。

此查询应该（我不确定）我的架构的表大小：

SELECT table_name AS "Table",
      ((data_length + index_length)) AS "Size"
FROM information_schema.TABLES
WHERE table_schema = "test"
ORDER BY (data_length + index_length) DESC;

但特定表在.之前和之后tokens总是返回49152ALTER TABLE

我可以假设即使使用 CHAR 和 utf8，如果只有 ASCII 字符，那么该字段不会为 utf8 预先分配空间？