SET NAMES utf8; SET time_zone = '+08:00'; SET foreign_key_checks = 0; SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; SET NAMES utf8mb4; DROP TABLE IF EXISTS `comment`; CREATE TABLE `comment` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `pid` int(11) unsigned NOT NULL, `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `username` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `time` datetime NOT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `pid` (`pid`), KEY `username` (`username`), CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `post` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `comment_ibfk_3` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; DELIMITER ;; CREATE TRIGGER `comment_add` AFTER INSERT ON `comment` FOR EACH ROW BEGIN UPDATE `post` SET `comment` = `comment` +1 WHERE `pid` = NEW.pid; END;; CREATE TRIGGER `comment_del` AFTER DELETE ON `comment` FOR EACH ROW BEGIN UPDATE `post` SET `comment` = `comment` -1 WHERE `pid` = OLD.pid; END;; DELIMITER ; DROP TABLE IF EXISTS `like`; CREATE TABLE `like` ( `username` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `pid` int(11) unsigned NOT NULL, KEY `pid` (`pid`), KEY `username` (`username`), CONSTRAINT `like_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `post` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `like_ibfk_2` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; DELIMITER ;; CREATE TRIGGER `like_add` AFTER INSERT ON `like` FOR EACH ROW BEGIN UPDATE `post` SET `like` = `like` +1 WHERE `pid` = NEW.pid; END;; CREATE TRIGGER `like_del` AFTER DELETE ON `like` FOR EACH ROW BEGIN UPDATE `post` SET `like` = `like` -1 WHERE `pid` = OLD.pid; END;; DELIMITER ; DROP TABLE IF EXISTS `notification`; CREATE TABLE `notification` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `time` datetime NOT NULL, `read` tinyint(1) NOT NULL DEFAULT '0', `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `username` (`username`), CONSTRAINT `notification_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; DROP TABLE IF EXISTS `post`; CREATE TABLE `post` ( `pid` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` text COLLATE utf8mb4_unicode_ci NOT NULL, `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `time` datetime NOT NULL, `username` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `like` int(11) unsigned NOT NULL DEFAULT '0', `comment` int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`pid`), KEY `username` (`username`), CONSTRAINT `post_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `muted` tinyint(1) NOT NULL DEFAULT '0', `level` tinyint(1) NOT NULL DEFAULT '0', `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `pwd` varchar(128) COLLATE utf8_unicode_ci NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;