Last active
November 17, 2018 04:41
-
-
Save wx-chevalier/ebd1ceb919a68e428e7901f7fc766f02 to your computer and use it in GitHub Desktop.
Demo Datebase for SQL Learning, Backend Application Example, MySQL Optimization, etc.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET NAMES utf8; | |
SET FOREIGN_KEY_CHECKS = 0; | |
-- ---------------------------- | |
-- Table structure for `component` | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `component`; | |
CREATE TABLE `component` ( | |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
`uuid` varchar(40) DEFAULT NULL, | |
`name` varchar(100) DEFAULT NULL, | |
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, | |
`updated_at` timestamp NULL DEFAULT NULL, | |
`deleted_at` timestamp NULL DEFAULT NULL, | |
PRIMARY KEY (`_id`), | |
KEY `unique` (`uuid`) USING BTREE, | |
KEY `search` (`name`) USING BTREE | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; | |
-- ---------------------------- | |
-- Table structure for `product` | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `product`; | |
CREATE TABLE `product` ( | |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '_ID,内部自增编号', | |
`uuid` varchar(40) DEFAULT NULL, | |
`code` varchar(6) DEFAULT NULL, | |
`name` varchar(15) DEFAULT NULL, | |
`category` varchar(15) DEFAULT NULL, | |
`price` decimal(4,2) DEFAULT NULL, | |
`created_at` datetime DEFAULT NULL, | |
`updated_at` datetime DEFAULT NULL, | |
`deleted_at` datetime DEFAULT NULL, | |
PRIMARY KEY (`_id`), | |
UNIQUE KEY `unique_product_in_category` (`name`,`category`) USING BTREE, | |
KEY `code` (`code`), | |
KEY `uuid` (`uuid`) | |
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; | |
-- ---------------------------- | |
-- Table structure for `product_component` | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `product_component`; | |
CREATE TABLE `product_component` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`uuid` varchar(40) DEFAULT NULL, | |
`product_uuid` varchar(40) DEFAULT NULL, | |
`component_uuid` varchar(40) DEFAULT NULL, | |
`created_at` timestamp NULL DEFAULT NULL, | |
`updated_at` timestamp NULL DEFAULT NULL, | |
`deleted_at` timestamp NULL DEFAULT NULL, | |
PRIMARY KEY (`_id`), | |
KEY `unique` (`product_uuid`,`component_uuid`) USING BTREE, | |
KEY `foreign` (`component_uuid`) USING BTREE, | |
CONSTRAINT `foreign_component` FOREIGN KEY (`component_uuid`) REFERENCES `component` (`uuid`) ON DELETE SET NULL ON UPDATE SET NULL, | |
CONSTRAINT `foreign_product` FOREIGN KEY (`product_uuid`) REFERENCES `product` (`uuid`) ON DELETE SET NULL ON UPDATE SET NULL | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; | |
-- ---------------------------- | |
-- Table structure for `user` | |
-- ---------------------------- | |
DROP TABLE IF EXISTS `user`; | |
CREATE TABLE `user` ( | |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '_ID,内部自增编号', | |
`uuid` varchar(40) DEFAULT NULL, | |
`name` varchar(32) DEFAULT NULL, | |
`password` varchar(64) DEFAULT NULL, | |
`salt` varchar(64) DEFAULT NULL, | |
`created_at` datetime DEFAULT NULL, | |
`updated_at` datetime DEFAULT NULL, | |
`deleted_at` datetime DEFAULT NULL, | |
PRIMARY KEY (`_id`), | |
KEY `uuid` (`uuid`) | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; | |
SET FOREIGN_KEY_CHECKS = 1; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment