Halo 博客网站的SQL
上一次网站被攻击,导致数据库被删,想用binglog恢复,需要存在表结构,于是我备份一下SQL
-- MySQL版本: 5.7.34
-- 数据库: `halodb`
--
-- --------------------------------------------------------
--
-- 表的结构 `attachments` 附件
--
CREATE TABLE `attachments` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`file_key` varchar(2047) DEFAULT NULL,
`height` int(11) DEFAULT '0',
`media_type` varchar(127) NOT NULL,
`name` varchar(255) NOT NULL,
`path` varchar(1023) NOT NULL,
`size` bigint(20) NOT NULL,
`suffix` varchar(50) DEFAULT NULL,
`thumb_path` varchar(1023) DEFAULT NULL,
`type` int(11) DEFAULT '0',
`width` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `categories` 分类表
--
CREATE TABLE `categories` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`parent_id` int(11) DEFAULT '0',
`slug` varchar(255) NOT NULL,
`slug_name` varchar(50) DEFAULT NULL,
`thumbnail` varchar(1023) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `comments` 评论表
--
CREATE TABLE `comments` (
`type` int(11) NOT NULL DEFAULT '0',
`id` bigint(20) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`allow_notification` bit(1) DEFAULT b'1',
`author` varchar(50) NOT NULL,
`author_url` varchar(511) DEFAULT NULL,
`content` varchar(1023) NOT NULL,
`email` varchar(255) NOT NULL,
`gravatar_md5` varchar(127) DEFAULT NULL,
`ip_address` varchar(127) DEFAULT NULL,
`is_admin` bit(1) DEFAULT b'0',
`parent_id` bigint(20) DEFAULT '0',
`post_id` int(11) NOT NULL,
`status` int(11) DEFAULT '1',
`top_priority` int(11) DEFAULT '0',
`user_agent` varchar(511) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
--
-- 转存表中的数据 `comments`
--
INSERT INTO `comments` (`type`, `id`, `create_time`, `update_time`, `allow_notification`, `author`, `author_url`, `content`, `email`, `gravatar_md5`, `ip_address`, `is_admin`, `parent_id`, `post_id`, `status`, `top_priority`, `user_agent`) VALUES
(0, 1, '2020-08-25 11:23:26.803000', '2020-09-03 14:19:29.753000', b'1', 'Halo', 'https://halo.run', '欢迎使用 Halo,这是你的第一条评论,头像来自 [Gravatar](https://cn.gravatar.com),你也可以通过注册 [Gravatar](https://cn.gravatar.com) 来显示自己的头像。', 'hi@halo.run', 'c99ef5c2e9621d48634f20afd24dbaa5', '112.92.93.214', b'0', 0, 1, 2, NULL, 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.109 Safari/537.36');
-- --------------------------------------------------------
--
-- 表的结构 `comment_black_list` 黑名单
--
CREATE TABLE `comment_black_list` (
`id` bigint(20) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`ban_time` datetime(6) DEFAULT NULL,
`ip_address` varchar(127) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `flyway_schema_history`
--
CREATE TABLE `flyway_schema_history` (
`installed_rank` int(11) NOT NULL,
`version` varchar(50) DEFAULT NULL,
`description` varchar(200) NOT NULL,
`type` varchar(20) NOT NULL,
`script` varchar(1000) NOT NULL,
`checksum` int(11) DEFAULT NULL,
`installed_by` varchar(100) NOT NULL,
`installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`execution_time` int(11) NOT NULL,
`success` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
--
-- 表的结构 `journals`
--
CREATE TABLE `journals` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`content` text NOT NULL,
`likes` bigint(20) DEFAULT '0',
`source_content` longtext NOT NULL,
`type` int(11) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `links` 友链
--
CREATE TABLE `links` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`logo` varchar(1023) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`priority` int(11) DEFAULT '0',
`team` varchar(255) DEFAULT NULL,
`url` varchar(1023) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `logs` 日志
--
CREATE TABLE `logs` (
`id` bigint(20) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`content` varchar(1023) NOT NULL,
`ip_address` varchar(127) DEFAULT NULL,
`log_key` varchar(1023) DEFAULT NULL,
`type` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
--
-- 表的结构 `menus` 目录表 数据是初始数据
--
CREATE TABLE `menus` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`icon` varchar(50) DEFAULT NULL,
`name` varchar(50) NOT NULL,
`parent_id` int(11) DEFAULT '0',
`priority` int(11) DEFAULT '0',
`target` varchar(20) DEFAULT '_self',
`team` varchar(255) DEFAULT NULL,
`url` varchar(1023) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
--
-- 转存表中的数据 `menus`
--
INSERT INTO `menus` (`id`, `create_time`, `update_time`, `icon`, `name`, `parent_id`, `priority`, `target`, `team`, `url`) VALUES
(1, '2020-08-25 11:23:26.921000', '2020-08-25 11:23:26.921000', '', '首页', 0, 1, '_self', '', '/'),
(2, '2020-08-25 11:23:26.959000', '2020-08-28 17:00:13.308000', '', '归档', 0, 2, '_self', '', '/archives'),
(3, '2020-08-25 11:23:26.971000', '2020-08-28 17:02:00.269000', '', '编辑中', 0, 3, '_self', '', '/categories/default'),
(4, '2020-08-25 11:23:26.985000', '2020-08-28 17:00:29.634000', '', '关于我', 0, 4, '_self', '', '/s/about');
-- --------------------------------------------------------
--
-- 表的结构 `metas`
--
CREATE TABLE `metas` (
`type` int(11) NOT NULL DEFAULT '0',
`id` bigint(20) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`meta_key` varchar(255) NOT NULL,
`post_id` int(11) NOT NULL,
`meta_value` varchar(1023) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `options`
--
CREATE TABLE `options` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`option_key` varchar(100) NOT NULL,
`type` int(11) DEFAULT '0',
`option_value` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
--
-- 表的结构 `photos`
--
CREATE TABLE `photos` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`location` varchar(255) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`take_time` datetime(6) DEFAULT NULL,
`team` varchar(255) DEFAULT NULL,
`thumbnail` varchar(1023) DEFAULT NULL,
`url` varchar(1023) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `posts` 文章保存位置 select original_content from posts
--
CREATE TABLE `posts` (
`type` int(11) NOT NULL DEFAULT '0',
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`disallow_comment` bit(1) DEFAULT b'0',
`edit_time` datetime(6) DEFAULT NULL,
`editor_type` int(11) DEFAULT '0',
`format_content` longtext,
`likes` bigint(20) DEFAULT '0',
`meta_description` varchar(1023) DEFAULT NULL,
`meta_keywords` varchar(511) DEFAULT NULL,
`original_content` longtext NOT NULL,
`password` varchar(255) DEFAULT NULL,
`slug` varchar(255) NOT NULL,
`status` int(11) DEFAULT '1',
`summary` longtext,
`template` varchar(255) DEFAULT NULL,
`thumbnail` varchar(1023) DEFAULT NULL,
`title` varchar(255) NOT NULL,
`top_priority` int(11) DEFAULT '0',
`url` varchar(255) DEFAULT NULL,
`visits` bigint(20) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `post_categories`
--
CREATE TABLE `post_categories` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`category_id` int(11) DEFAULT NULL,
`post_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `post_tags`
--
CREATE TABLE `post_tags` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`post_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `tags`
--
CREATE TABLE `tags` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`slug` varchar(50) NOT NULL,
`slug_name` varchar(255) DEFAULT NULL,
`thumbnail` varchar(1023) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `theme_settings`
--
CREATE TABLE `theme_settings` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`setting_key` varchar(255) NOT NULL,
`theme_id` varchar(255) NOT NULL,
`setting_value` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- --------------------------------------------------------
--
-- 表的结构 `users`
--
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`avatar` varchar(1023) DEFAULT NULL,
`description` varchar(1023) DEFAULT NULL,
`email` varchar(127) DEFAULT NULL,
`expire_time` datetime(6) DEFAULT NULL,
`mfa_key` varchar(64) DEFAULT NULL,
`mfa_type` int(11) NOT NULL DEFAULT '0',
`nickname` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`username` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
--
-- 转存表中的数据 `users`
--
INSERT INTO `users` (`id`, `create_time`, `update_time`, `avatar`, `description`, `email`, `expire_time`, `mfa_key`, `mfa_type`, `nickname`, `password`, `username`) VALUES
(1, '2020-06-25 11:23:25.512000', '2020-06-25 11:23:25.512000', '//cn.gravatar.com/avatar/c7364e0be9669eea2bca79dfd01c6a46?s=256&d=mm', 'encrypted passwords', 'xxxx@qq.com', '2020-08-25 11:23:25.512000', NULL, 0, 'yuyege', '', 'myblogusername');
--
-- 转储表的索引
--
--
-- 表的索引 `attachments`
--
ALTER TABLE `attachments`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `attachments_media_type` (`media_type`) USING BTREE,
ADD KEY `attachments_create_time` (`create_time`) USING BTREE;
--
-- 表的索引 `categories`
--
ALTER TABLE `categories`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD UNIQUE KEY `UK_oul14ho7bctbefv8jywp5v3i2` (`slug`) USING BTREE,
ADD KEY `categories_name` (`name`) USING BTREE,
ADD KEY `categories_parent_id` (`parent_id`) USING BTREE;
--
-- 表的索引 `comments`
--
ALTER TABLE `comments`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `comments_post_id` (`post_id`) USING BTREE,
ADD KEY `comments_type_status` (`type`,`status`) USING BTREE,
ADD KEY `comments_parent_id` (`parent_id`) USING BTREE;
--
-- 表的索引 `comment_black_list`
--
ALTER TABLE `comment_black_list`
ADD PRIMARY KEY (`id`) USING BTREE;
--
-- 表的索引 `flyway_schema_history`
--
ALTER TABLE `flyway_schema_history`
ADD PRIMARY KEY (`installed_rank`) USING BTREE,
ADD KEY `flyway_schema_history_s_idx` (`success`) USING BTREE;
--
-- 表的索引 `journals`
--
ALTER TABLE `journals`
ADD PRIMARY KEY (`id`) USING BTREE;
--
-- 表的索引 `links`
--
ALTER TABLE `links`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `links_name` (`name`) USING BTREE;
--
-- 表的索引 `logs`
--
ALTER TABLE `logs`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `logs_create_time` (`create_time`) USING BTREE;
--
-- 表的索引 `menus`
--
ALTER TABLE `menus`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `menus_parent_id` (`parent_id`) USING BTREE,
ADD KEY `menus_name` (`name`) USING BTREE;
--
-- 表的索引 `metas`
--
ALTER TABLE `metas`
ADD PRIMARY KEY (`id`) USING BTREE;
--
-- 表的索引 `options`
--
ALTER TABLE `options`
ADD PRIMARY KEY (`id`) USING BTREE;
--
-- 表的索引 `photos`
--
ALTER TABLE `photos`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `photos_team` (`team`) USING BTREE,
ADD KEY `photos_create_time` (`create_time`) USING BTREE;
--
-- 表的索引 `posts`
--
ALTER TABLE `posts`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD UNIQUE KEY `UK_qmmso8qxjpbxwegdtp0l90390` (`slug`) USING BTREE,
ADD KEY `posts_type_status` (`type`,`status`) USING BTREE,
ADD KEY `posts_create_time` (`create_time`) USING BTREE;
--
-- 表的索引 `post_categories`
--
ALTER TABLE `post_categories`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `post_categories_post_id` (`post_id`) USING BTREE,
ADD KEY `post_categories_category_id` (`category_id`) USING BTREE;
--
-- 表的索引 `post_tags`
--
ALTER TABLE `post_tags`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `post_tags_post_id` (`post_id`) USING BTREE,
ADD KEY `post_tags_tag_id` (`tag_id`) USING BTREE;
--
-- 表的索引 `tags`
--
ALTER TABLE `tags`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD UNIQUE KEY `UK_sn0d91hxu700qcw0n4pebp5vc` (`slug`) USING BTREE,
ADD KEY `tags_name` (`name`) USING BTREE;
--
-- 表的索引 `theme_settings`
--
ALTER TABLE `theme_settings`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD KEY `theme_settings_setting_key` (`setting_key`) USING BTREE,
ADD KEY `theme_settings_theme_id` (`theme_id`) USING BTREE;
--
-- 表的索引 `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`) USING BTREE;
--
-- 在导出的表使用AUTO_INCREMENT
--
--
-- 使用表AUTO_INCREMENT `attachments`
--
ALTER TABLE `attachments`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=136;
--
-- 使用表AUTO_INCREMENT `categories`
--
ALTER TABLE `categories`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
--
-- 使用表AUTO_INCREMENT `comments`
--
ALTER TABLE `comments`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- 使用表AUTO_INCREMENT `comment_black_list`
--
ALTER TABLE `comment_black_list`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
--
-- 使用表AUTO_INCREMENT `journals`
--
ALTER TABLE `journals`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- 使用表AUTO_INCREMENT `links`
--
ALTER TABLE `links`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- 使用表AUTO_INCREMENT `logs`
--
ALTER TABLE `logs`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=553;
--
-- 使用表AUTO_INCREMENT `menus`
--
ALTER TABLE `menus`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- 使用表AUTO_INCREMENT `metas`
--
ALTER TABLE `metas`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
--
-- 使用表AUTO_INCREMENT `options`
--
ALTER TABLE `options`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=62;
--
-- 使用表AUTO_INCREMENT `photos`
--
ALTER TABLE `photos`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- 使用表AUTO_INCREMENT `posts`
--
ALTER TABLE `posts`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=63;
--
-- 使用表AUTO_INCREMENT `post_categories`
--
ALTER TABLE `post_categories`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=184;
--
-- 使用表AUTO_INCREMENT `post_tags`
--
ALTER TABLE `post_tags`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- 使用表AUTO_INCREMENT `tags`
--
ALTER TABLE `tags`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- 使用表AUTO_INCREMENT `theme_settings`
--
ALTER TABLE `theme_settings`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=128;
--
-- 使用表AUTO_INCREMENT `users`
--
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;
三年前(2018)觉得有价值的一个sql
mysql -uroot -p
show databases;
use mmall;
source /etc/sql/mmall.sql 导入sql
DROP TABLE IF EXISTS `mmall_cart`;
CREATE TABLE `mmall_cart` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`product_id` int(11) DEFAULT NULL COMMENT '商品id',
`quantity` int(11) DEFAULT NULL COMMENT '数量',
`checked` int(11) DEFAULT NULL COMMENT '是否选择,1=已勾选,0=未勾选',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `user_id_index` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=146 DEFAULT CHARSET=utf8;
#ENGINE=InnoDB将mysql数据库引擎设置为InnoDB。InnoDB表示带行锁定和外键的事务安全表。
#AUTO_INCREMENT=146 表示自增字段的值,每次自增146
#CHECKSUM=1:设为1表示对所有行进行实时检验求和,用于mysql检测主从数据一致性
#DELAY_KEY_WRITE=1 :设置为1表示在表关闭之前,将对表的update操作更新数据到磁盘,不更新索引到磁盘,把对索引的更改记录在内存,这样可以使索引更新更快。在关闭表的时候,一起更新索引到磁盘
#ROW_FORMAT=DYNAMIC:在sql里面,如果一张表不存在varchar、text、及其变形、blob及其变形的字段的话,这样的表就是静态表,即ROW_FORMAT=FIXED,每条记录所占用的字节一样;否则是动态表,每条记录所占用的字节是动态的,优点是节省空间,缺点是增加读取的时间的开销。
BEGIN;
INSERT INTO `mmall_cart` VALUES ('126', '21', '26', '1', '1', '2017-04-13 21:27:06', '2017-04-13 21:27:06');
COMMIT;
DROP TABLE IF EXISTS `mmall_category`;
CREATE TABLE `mmall_category` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '类别Id',
`parent_id` int(11) DEFAULT NULL COMMENT '父类别id当id=0时说明是根节点,一级类别',
`name` varchar(50) DEFAULT NULL COMMENT '类别名称',
`status` tinyint(1) DEFAULT '1' COMMENT '类别状态1-正常,2-已废弃',
`sort_order` int(4) DEFAULT NULL COMMENT '排序编号,同类展示顺序,数值相等则自然排序',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100032 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `mmall_category` VALUES ('100001', '0', '家用电器', '1', null, '2017-03-25 16:46:00', '2017-03-25 16:46:00'), ('100002', '0', '数码3C', '1', null, '2017-03-25 16:46:21', '2017-03-25 16:46:21'), ('100003', '0', '服装箱包', '1', null, '2017-03-25 16:49:53', '2017-03-25 16:49:53'), ('100004', '0', '食品生鲜', '1', null, '2017-03-25 16:50:19', '2017-03-25 16:50:19'), ('100005', '0', '酒水饮料', '1', null, '2017-03-25 16:50:29', '2017-03-25 16:50:29'), ('100006', '100001', '冰箱', '1', null, '2017-03-25 16:52:15', '2017-03-25 16:52:15'), ('100007', '100001', '电视', '1', null, '2017-03-25 16:52:26', '2017-03-25 16:52:26'), ('100008', '100001', '洗衣机', '1', null, '2017-03-25 16:52:39', '2017-03-25 16:52:39'), ('100009', '100001', '空调', '1', null, '2017-03-25 16:52:45', '2017-03-25 16:52:45'), ('100010', '100001', '电热水器', '1', null, '2017-03-25 16:52:54', '2017-03-25 16:52:54'), ('100011', '100002', '电脑', '1', null, '2017-03-25 16:53:18', '2017-03-25 16:53:18'), ('100012', '100002', '手机', '1', null, '2017-03-25 16:53:27', '2017-03-25 16:53:27'), ('100013', '100002', '平板电脑', '1', null, '2017-03-25 16:53:35', '2017-03-25 16:53:35'), ('100014', '100002', '数码相机', '1', null, '2017-03-25 16:53:56', '2017-03-25 16:53:56'), ('100015', '100002', '3C配件', '1', null, '2017-03-25 16:54:07', '2017-03-25 16:54:07'), ('100016', '100003', '女装', '1', null, '2017-03-25 16:54:44', '2017-03-25 16:54:44'), ('100017', '100003', '帽子', '1', null, '2017-03-25 16:54:51', '2017-03-25 16:54:51'), ('100018', '100003', '旅行箱', '1', null, '2017-03-25 16:55:02', '2017-03-25 16:55:02'), ('100019', '100003', '手提包', '1', null, '2017-03-25 16:55:09', '2017-03-25 16:55:09'), ('100020', '100003', '保暖内衣', '1', null, '2017-03-25 16:55:18', '2017-03-25 16:55:18'), ('100021', '100004', '零食', '1', null, '2017-03-25 16:55:30', '2017-03-25 16:55:30'), ('100022', '100004', '生鲜', '1', null, '2017-03-25 16:55:37', '2017-03-25 16:55:37'), ('100023', '100004', '半成品菜', '1', null, '2017-03-25 16:55:47', '2017-03-25 16:55:47'), ('100024', '100004', '速冻食品', '1', null, '2017-03-25 16:55:56', '2017-03-25 16:55:56'), ('100025', '100004', '进口食品', '1', null, '2017-03-25 16:56:06', '2017-03-25 16:56:06'), ('100026', '100005', '白酒', '1', null, '2017-03-25 16:56:22', '2017-03-25 16:56:22'), ('100027', '100005', '红酒', '1', null, '2017-03-25 16:56:30', '2017-03-25 16:56:30'), ('100028', '100005', '饮料', '1', null, '2017-03-25 16:56:37', '2017-03-25 16:56:37'), ('100029', '100005', '调制鸡尾酒', '1', null, '2017-03-25 16:56:45', '2017-03-25 16:56:45'), ('100030', '100005', '进口洋酒', '1', null, '2017-03-25 16:57:05', '2017-03-25 16:57:05');
COMMIT;
DROP TABLE IF EXISTS `mmall_order`;
CREATE TABLE `mmall_order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
`order_no` bigint(20) DEFAULT NULL COMMENT '订单号',
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`shipping_id` int(11) DEFAULT NULL,
`payment` decimal(20,2) DEFAULT NULL COMMENT '实际付款金额,单位是元,保留两位小数',
`payment_type` int(4) DEFAULT NULL COMMENT '支付类型,1-在线支付',
`postage` int(10) DEFAULT NULL COMMENT '运费,单位是元',
`status` int(10) DEFAULT NULL COMMENT '订单状态:0-已取消-10-未付款,20-已付款,40-已发货,50-交易成功,60-交易关闭',
`payment_time` datetime DEFAULT NULL COMMENT '支付时间',
`send_time` datetime DEFAULT NULL COMMENT '发货时间',
`end_time` datetime DEFAULT NULL COMMENT '交易完成时间',
`close_time` datetime DEFAULT NULL COMMENT '交易关闭时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `order_no_index` (`order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=118 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `mmall_order` VALUES ('103', '1491753014256', '1', '25', '13998.00', '1', '0', '10', null, null, null, null, '2017-04-09 23:50:14', '2017-04-09 23:50:14'), ('104', '1491830695216', '1', '26', '13998.00', '1', '0', '10', null, null, null, null, '2017-04-10 21:24:55', '2017-04-10 21:24:55'), ('105', '1492089528889', '1', '29', '3299.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:18:48', '2017-04-13 21:18:48'), ('106', '1492090946105', '1', '29', '27894.00', '1', '0', '20', '2017-04-13 21:42:40', null, null, null, '2017-04-13 21:42:26', '2017-04-13 21:42:41'), ('107', '1492091003128', '1', '29', '8597.00', '1', '0', '20', '2017-04-13 21:43:38', null, null, null, '2017-04-13 21:43:23', '2017-04-13 21:43:38'), ('108', '1492091051313', '1', '29', '1999.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:44:11', '2017-04-13 21:44:11'), ('109', '1492091061513', '1', '29', '6598.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:44:21', '2017-04-13 21:44:21'), ('110', '1492091069563', '1', '29', '3299.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:44:29', '2017-04-13 21:44:29'), ('111', '1492091076073', '1', '29', '4299.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:44:36', '2017-04-13 21:44:36'), ('112', '1492091083720', '1', '29', '3299.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:44:43', '2017-04-13 21:44:43'), ('113', '1492091089794', '1', '29', '6999.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:44:49', '2017-04-13 21:44:49'), ('114', '1492091096400', '1', '29', '6598.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:44:56', '2017-04-13 21:44:56'), ('115', '1492091102371', '1', '29', '3299.00', '1', '0', '10', null, null, null, null, '2017-04-13 21:45:02', '2017-04-13 21:45:02'), ('116', '1492091110004', '1', '29', '8598.00', '1', '0', '40', '2017-04-13 21:55:16', '2017-04-13 21:55:31', null, null, '2017-04-13 21:45:09', '2017-04-13 21:55:31'), ('117', '1492091141269', '1', '29', '22894.00', '1', '0', '20', '2017-04-13 21:46:06', null, null, null, '2017-04-13 21:45:41', '2017-04-13 21:46:07');
COMMIT;
DROP TABLE IF EXISTS `mmall_order_item`;
CREATE TABLE `mmall_order_item` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单子表id',
`user_id` int(11) DEFAULT NULL,
`order_no` bigint(20) DEFAULT NULL,
`product_id` int(11) DEFAULT NULL COMMENT '商品id',
`product_name` varchar(100) DEFAULT NULL COMMENT '商品名称',
`product_image` varchar(500) DEFAULT NULL COMMENT '商品图片地址',
`current_unit_price` decimal(20,2) DEFAULT NULL COMMENT '生成订单时的商品单价,单位是元,保留两位小数',
`quantity` int(10) DEFAULT NULL COMMENT '商品数量',
`total_price` decimal(20,2) DEFAULT NULL COMMENT '商品总价,单位是元,保留两位小数',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `order_no_index` (`order_no`) USING BTREE,
KEY `order_no_user_id_index` (`user_id`,`order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `mmall_order_item` VALUES ('113', '1', '1491753014256', '26', 'Apple iPhone 7 Plus (A1661) 128G 玫瑰金色 移动联通电信4G手机', '241997c4-9e62-4824-b7f0-7425c3c28917.jpeg', '6999.00', '2', '13998.00', '2017-04-09 23:50:14', '2017-04-09 23:50:14'), ('114', '1', '1491830695216', '26', 'Apple iPhone 7 Plus (A1661) 128G 玫瑰金色 移动联通电信4G手机', '241997c4-9e62-4824-b7f0-7425c3c28917.jpeg', '6999.00', '2', '13998.00', '2017-04-10 21:24:55', '2017-04-10 21:24:55'), ('115', '1', '1492089528889', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '1', '3299.00', '2017-04-13 21:18:48', '2017-04-13 21:18:48'), ('116', '1', '1492090946105', '29', 'Haier/海尔HJ100-1HU1 10公斤滚筒洗衣机全自动带烘干家用大容量 洗烘一体', '173335a4-5dce-4afd-9f18-a10623724c4e.jpeg', '4299.00', '2', '8598.00', '2017-04-13 21:42:26', '2017-04-13 21:42:26'), ('117', '1', '1492090946105', '28', '4+64G送手环/Huawei/华为 nova 手机P9/P10plus青春', '0093f5d3-bdb4-4fb0-bec5-5465dfd26363.jpeg', '1999.00', '1', '1999.00', '2017-04-13 21:42:26', '2017-04-13 21:42:26'), ('118', '1', '1492090946105', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '1', '3299.00', '2017-04-13 21:42:26', '2017-04-13 21:42:26'), ('119', '1', '1492090946105', '26', 'Apple iPhone 7 Plus (A1661) 128G 玫瑰金色 移动联通电信4G手机', '241997c4-9e62-4824-b7f0-7425c3c28917.jpeg', '6999.00', '2', '13998.00', '2017-04-13 21:42:26', '2017-04-13 21:42:26'), ('120', '1', '1492091003128', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '2', '6598.00', '2017-04-13 21:43:23', '2017-04-13 21:43:23'), ('121', '1', '1492091003128', '28', '4+64G送手环/Huawei/华为 nova 手机P9/P10plus青春', '0093f5d3-bdb4-4fb0-bec5-5465dfd26363.jpeg', '1999.00', '1', '1999.00', '2017-04-13 21:43:23', '2017-04-13 21:43:23'), ('122', '1', '1492091051313', '28', '4+64G送手环/Huawei/华为 nova 手机P9/P10plus青春', '0093f5d3-bdb4-4fb0-bec5-5465dfd26363.jpeg', '1999.00', '1', '1999.00', '2017-04-13 21:44:11', '2017-04-13 21:44:11'), ('123', '1', '1492091061513', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '2', '6598.00', '2017-04-13 21:44:21', '2017-04-13 21:44:21'), ('124', '1', '1492091069563', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '1', '3299.00', '2017-04-13 21:44:29', '2017-04-13 21:44:29'), ('125', '1', '1492091076073', '29', 'Haier/海尔HJ100-1HU1 10公斤滚筒洗衣机全自动带烘干家用大容量 洗烘一体', '173335a4-5dce-4afd-9f18-a10623724c4e.jpeg', '4299.00', '1', '4299.00', '2017-04-13 21:44:36', '2017-04-13 21:44:36'), ('126', '1', '1492091083720', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '1', '3299.00', '2017-04-13 21:44:43', '2017-04-13 21:44:43'), ('127', '1', '1492091089794', '26', 'Apple iPhone 7 Plus (A1661) 128G 玫瑰金色 移动联通电信4G手机', '241997c4-9e62-4824-b7f0-7425c3c28917.jpeg', '6999.00', '1', '6999.00', '2017-04-13 21:44:49', '2017-04-13 21:44:49'), ('128', '1', '1492091096400', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '2', '6598.00', '2017-04-13 21:44:56', '2017-04-13 21:44:56'), ('129', '1', '1492091102371', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '1', '3299.00', '2017-04-13 21:45:02', '2017-04-13 21:45:02'), ('130', '1', '1492091110004', '29', 'Haier/海尔HJ100-1HU1 10公斤滚筒洗衣机全自动带烘干家用大容量 洗烘一体', '173335a4-5dce-4afd-9f18-a10623724c4e.jpeg', '4299.00', '2', '8598.00', '2017-04-13 21:45:09', '2017-04-13 21:45:09'), ('131', '1', '1492091141269', '26', 'Apple iPhone 7 Plus (A1661) 128G 玫瑰金色 移动联通电信4G手机', '241997c4-9e62-4824-b7f0-7425c3c28917.jpeg', '6999.00', '1', '6999.00', '2017-04-13 21:45:41', '2017-04-13 21:45:41'), ('132', '1', '1492091141269', '27', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', '3299.00', '1', '3299.00', '2017-04-13 21:45:41', '2017-04-13 21:45:41'), ('133', '1', '1492091141269', '29', 'Haier/海尔HJ100-1HU1 10公斤滚筒洗衣机全自动带烘干家用大容量 洗烘一体', '173335a4-5dce-4afd-9f18-a10623724c4e.jpeg', '4299.00', '2', '8598.00', '2017-04-13 21:45:41', '2017-04-13 21:45:41'), ('134', '1', '1492091141269', '28', '4+64G送手环/Huawei/华为 nova 手机P9/P10plus青春', '0093f5d3-bdb4-4fb0-bec5-5465dfd26363.jpeg', '1999.00', '2', '3998.00', '2017-04-13 21:45:41', '2017-04-13 21:45:41');
COMMIT;
-- ----------------------------
-- Table structure for `mmall_pay_info`
-- ----------------------------
DROP TABLE IF EXISTS `mmall_pay_info`;
CREATE TABLE `mmall_pay_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`order_no` bigint(20) DEFAULT NULL COMMENT '订单号',
`pay_platform` int(10) DEFAULT NULL COMMENT '支付平台:1-支付宝,2-微信',
`platform_number` varchar(200) DEFAULT NULL COMMENT '支付宝支付流水号',
`platform_status` varchar(20) DEFAULT NULL COMMENT '支付宝支付状态',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `mmall_pay_info` VALUES ('53', '1', '1492090946105', '1', '2017041321001004300200116250', 'WAIT_BUYER_PAY', '2017-04-13 21:42:33', '2017-04-13 21:42:33'), ('54', '1', '1492090946105', '1', '2017041321001004300200116250', 'TRADE_SUCCESS', '2017-04-13 21:42:41', '2017-04-13 21:42:41'), ('55', '1', '1492091003128', '1', '2017041321001004300200116251', 'WAIT_BUYER_PAY', '2017-04-13 21:43:31', '2017-04-13 21:43:31'), ('56', '1', '1492091003128', '1', '2017041321001004300200116251', 'TRADE_SUCCESS', '2017-04-13 21:43:38', '2017-04-13 21:43:38'), ('57', '1', '1492091141269', '1', '2017041321001004300200116252', 'WAIT_BUYER_PAY', '2017-04-13 21:45:59', '2017-04-13 21:45:59'), ('58', '1', '1492091141269', '1', '2017041321001004300200116252', 'TRADE_SUCCESS', '2017-04-13 21:46:07', '2017-04-13 21:46:07'), ('59', '1', '1492091110004', '1', '2017041321001004300200116396', 'WAIT_BUYER_PAY', '2017-04-13 21:55:08', '2017-04-13 21:55:08'), ('60', '1', '1492091110004', '1', '2017041321001004300200116396', 'TRADE_SUCCESS', '2017-04-13 21:55:17', '2017-04-13 21:55:17');
COMMIT;
DROP TABLE IF EXISTS `mmall_product`;
CREATE TABLE `mmall_product` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
`category_id` int(11) NOT NULL COMMENT '分类id,对应mmall_category表的主键',
`name` varchar(100) NOT NULL COMMENT '商品名称',
`subtitle` varchar(200) DEFAULT NULL COMMENT '商品副标题',
`main_image` varchar(500) DEFAULT NULL COMMENT '产品主图,url相对地址',
`sub_images` text COMMENT '图片地址,json格式,扩展用',
`detail` text COMMENT '商品详情',
`price` decimal(20,2) NOT NULL COMMENT '价格,单位-元保留两位小数',
`stock` int(11) NOT NULL COMMENT '库存数量',
`status` int(6) DEFAULT '1' COMMENT '商品状态.1-在售 2-下架 3-删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `mmall_product` VALUES ('26', '100002', 'Apple iPhone 7 Plus (A1661) 128G 玫瑰金色 移动联通电信4G手机', 'iPhone 7,现更以红色呈现。', '241997c4-9e62-4824-b7f0-7425c3c28917.jpeg', '241997c4-9e62-4824-b7f0-7425c3c28917.jpeg,b6c56eb0-1748-49a9-98dc-bcc4b9788a54.jpeg,92f17532-1527-4563-aa1d-ed01baa0f7b2.jpeg,3adbe4f7-e374-4533-aa79-cc4a98c529bf.jpeg', '<p><img alt=\"10000.jpg\" src=\"http://img.happymmall.com/00bce8d4-e9af-4c8d-b205-e6c75c7e252b.jpg\" width=\"790\" height=\"553\"><br></p><p><img alt=\"20000.jpg\" src=\"http://img.happymmall.com/4a70b4b4-01ee-46af-9468-31e67d0995b8.jpg\" width=\"790\" height=\"525\"><br></p><p><img alt=\"30000.jpg\" src=\"http://img.happymmall.com/0570e033-12d7-49b2-88f3-7a5d84157223.jpg\" width=\"790\" height=\"365\"><br></p><p><img alt=\"40000.jpg\" src=\"http://img.happymmall.com/50515c02-3255-44b9-a829-9e141a28c08a.jpg\" width=\"790\" height=\"525\"><br></p><p><img alt=\"50000.jpg\" src=\"http://img.happymmall.com/c138fc56-5843-4287-a029-91cf3732d034.jpg\" width=\"790\" height=\"525\"><br></p><p><img alt=\"60000.jpg\" src=\"http://img.happymmall.com/c92d1f8a-9827-453f-9d37-b10a3287e894.jpg\" width=\"790\" height=\"525\"><br></p><p><br></p><p><img alt=\"TB24p51hgFkpuFjSspnXXb4qFXa-1776456424.jpg\" src=\"http://img.happymmall.com/bb1511fc-3483-471f-80e5-c7c81fa5e1dd.jpg\" width=\"790\" height=\"375\"><br></p><p><br></p><p><img alt=\"shouhou.jpg\" src=\"http://img.happymmall.com/698e6fbe-97ea-478b-8170-008ad24030f7.jpg\" width=\"750\" height=\"150\"><br></p><p><img alt=\"999.jpg\" src=\"http://img.happymmall.com/ee276fe6-5d79-45aa-8393-ba1d210f9c89.jpg\" width=\"790\" height=\"351\"><br></p>', '6999.00', '9991', '1', null, '2017-04-13 21:45:41'), ('27', '100006', 'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用', '送品牌烤箱,五一大促', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg', 'ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg,4bb02f1c-62d5-48cc-b358-97b05af5740d.jpeg,36bdb49c-72ae-4185-9297-78829b54b566.jpeg', '<p><img alt=\"miaoshu.jpg\" src=\"http://img.happymmall.com/9c5c74e6-6615-4aa0-b1fc-c17a1eff6027.jpg\" width=\"790\" height=\"444\"><br></p><p><img alt=\"miaoshu2.jpg\" src=\"http://img.happymmall.com/31dc1a94-f354-48b8-a170-1a1a6de8751b.jpg\" width=\"790\" height=\"1441\"><img alt=\"miaoshu3.jpg\" src=\"http://img.happymmall.com/7862594b-3063-4b52-b7d4-cea980c604e0.jpg\" width=\"790\" height=\"1442\"><img alt=\"miaoshu4.jpg\" src=\"http://img.happymmall.com/9a650563-dc85-44d6-b174-d6960cfb1d6a.jpg\" width=\"790\" height=\"1441\"><br></p>', '3299.00', '8876', '1', '2017-04-13 18:51:54', '2017-04-13 21:45:41'), ('28', '100012', '4+64G送手环/Huawei/华为 nova 手机P9/P10plus青春', 'NOVA青春版1999元', '0093f5d3-bdb4-4fb0-bec5-5465dfd26363.jpeg', '0093f5d3-bdb4-4fb0-bec5-5465dfd26363.jpeg,13da2172-4445-4eb5-a13f-c5d4ede8458c.jpeg,58d5d4b7-58d4-4948-81b6-2bae4f79bf02.jpeg', '<p><img alt=\"11TB2fKK3cl0kpuFjSsziXXa.oVXa_!!1777180618.jpg\" src=\"http://img.happymmall.com/5c2d1c6d-9e09-48ce-bbdb-e833b42ff664.jpg\" width=\"790\" height=\"966\"><img alt=\"22TB2YP3AkEhnpuFjSZFpXXcpuXXa_!!1777180618.jpg\" src=\"http://img.happymmall.com/9a10b877-818f-4a27-b6f7-62887f3fb39d.jpg\" width=\"790\" height=\"1344\"><img alt=\"33TB2Yyshk.hnpuFjSZFpXXcpuXXa_!!1777180618.jpg\" src=\"http://img.happymmall.com/7d7fbd69-a3cb-4efe-8765-423bf8276e3e.jpg\" width=\"790\" height=\"700\"><img alt=\"TB2diyziB8kpuFjSspeXXc7IpXa_!!1777180618.jpg\" src=\"http://img.happymmall.com/1d7160d2-9dba-422f-b2a0-e92847ba6ce9.jpg\" width=\"790\" height=\"393\"><br></p>', '1999.00', '9994', '1', '2017-04-13 18:57:18', '2017-04-13 21:45:41'), ('29', '100008', 'Haier/海尔HJ100-1HU1 10公斤滚筒洗衣机全自动带烘干家用大容量 洗烘一体', '门店机型 德邦送货', '173335a4-5dce-4afd-9f18-a10623724c4e.jpeg', '173335a4-5dce-4afd-9f18-a10623724c4e.jpeg,42b1b8bc-27c7-4ee1-80ab-753d216a1d49.jpeg,2f1b3de1-1eb1-4c18-8ca2-518934931bec.jpeg', '<p><img alt=\"1TB2WLZrcIaK.eBjSspjXXXL.XXa_!!2114960396.jpg\" src=\"http://img.happymmall.com/ffcce953-81bd-463c-acd1-d690b263d6df.jpg\" width=\"790\" height=\"920\"><img alt=\"2TB2zhOFbZCO.eBjSZFzXXaRiVXa_!!2114960396.jpg\" src=\"http://img.happymmall.com/58a7bd25-c3e7-4248-9dba-158ef2a90e70.jpg\" width=\"790\" height=\"1052\"><img alt=\"3TB27mCtb7WM.eBjSZFhXXbdWpXa_!!2114960396.jpg\" src=\"http://img.happymmall.com/2edbe9b3-28be-4a8b-a9c3-82e40703f22f.jpg\" width=\"790\" height=\"820\"><br></p>', '4299.00', '9993', '1', '2017-04-13 19:07:47', '2017-04-13 21:45:41');
COMMIT;
DROP TABLE IF EXISTS `mmall_shipping`;
CREATE TABLE `mmall_shipping` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`receiver_name` varchar(20) DEFAULT NULL COMMENT '收货姓名',
`receiver_phone` varchar(20) DEFAULT NULL COMMENT '收货固定电话',
`receiver_mobile` varchar(20) DEFAULT NULL COMMENT '收货移动电话',
`receiver_province` varchar(20) DEFAULT NULL COMMENT '省份',
`receiver_city` varchar(20) DEFAULT NULL COMMENT '城市',
`receiver_district` varchar(20) DEFAULT NULL COMMENT '区/县',
`receiver_address` varchar(200) DEFAULT NULL COMMENT '详细地址',
`receiver_zip` varchar(6) DEFAULT NULL COMMENT '邮编',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `mmall_shipping` VALUES ('4', '13', 'geely', '010', '18688888888', '北京', '北京市', '海淀区', '中关村', '100000', '2017-01-22 14:26:25', '2017-01-22 14:26:25'), ('7', '17', 'Rosen', '13800138000', '13800138000', '北京', '北京', null, '中关村', '100000', '2017-03-29 12:11:01', '2017-03-29 12:11:01'), ('29', '1', '吉利', '13800138000', '13800138000', '北京', '北京', '海淀区', '海淀区中关村', '100000', '2017-04-09 18:33:32', '2017-04-09 18:33:32');
COMMIT;
DROP TABLE IF EXISTS `mmall_user`;
CREATE TABLE `mmall_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户表id',
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(50) NOT NULL COMMENT '用户密码,MD5加密',
`email` varchar(50) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`question` varchar(100) DEFAULT NULL COMMENT '找回密码问题',
`answer` varchar(100) DEFAULT NULL COMMENT '找回密码答案',
`role` int(4) NOT NULL COMMENT '角色0-管理员,1-普通用户',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '最后一次更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `user_name_unique` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `mmall_user` VALUES ('1', 'admin', '427338237BD929443EC5D48E24FD2B1A', 'admin@happymmall.com', '13800138000', '问题', '答案', '1', '2016-11-06 16:56:45', '2017-04-04 19:27:36'), ('13', 'geely', '08E9A6EA287E70E7E3F7C982BF7923AC', 'geely@happymmall.com', '13800138000', '问题', '答案', '0', '2016-11-19 22:19:25', '2016-11-19 22:19:25'), ('17', 'rosen', '095AC193FE2212EEC7A93E8FEFF11902', 'rosen1@happymmall.com', '13800138000', '问题', '答案', '0', '2017-03-17 10:51:33', '2017-04-09 23:13:26'), ('21', 'soonerbetter', 'DE6D76FE7C40D5A1A8F04213F2BEFBEE', 'test06@happymmall.com', '13800138000', '105204', '105204', '0', '2017-04-13 21:26:22', '2017-04-13 21:26:22');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
生成日历表 calendar
CREATE TABLE num (i int);-- 创建一个表用来储存0-9的数字
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- 生成0-9的数字,方便以后计算时间
CREATE TABLE if not exists calendar(datelist date); -- 生成一个存储日期的表,datalist是字段名
-- 这里是生成并插入日期数据
INSERT INTO calendar(datelist) SELECT
adddate(
( -- 这里的起始日期,你可以换成当前日期
DATE_FORMAT("2019-1-1", '%Y-%m-%d')
),
numlist.id
) AS `date`
FROM
(
SELECT
n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
FROM
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
CROSS JOIN num AS n1000
CROSS JOIN num AS n10000
) AS numlist;
学慕课网视频的电商项目数据库
DROP TABLE IF EXISTS `mall_category`;
CREATE TABLE `mall_category` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '类别Id',
`parent_id` int(11) DEFAULT NULL COMMENT '父类别id当id=0时说明是根节点,一级类别',
`name` varchar(50) DEFAULT NULL COMMENT '类别名称',
`status` tinyint(1) DEFAULT '1' COMMENT '类别状态1-正常,2-已废弃',
`sort_order` int(4) DEFAULT NULL COMMENT '排序编号,同类展示顺序,数值相等则自然排序',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `mall_category` (`id`, `parent_id`, `name`, `status`, `sort_order`, `create_time`, `update_time`)
VALUES
(100001,0,'家用电器',1,NULL,'2000-03-25 16:46:00','2000-03-25 16:46:00'),
(100002,0,'数码3C',1,NULL,'2000-03-25 16:46:21','2000-03-25 16:46:21'),
(100003,0,'服装箱包',1,NULL,'2000-03-25 16:49:53','2000-03-25 16:49:53'),
(100004,0,'食品生鲜',1,NULL,'2000-03-25 16:50:19','2000-03-25 16:50:19'),
(100005,0,'酒水饮料',1,NULL,'2000-03-25 16:50:29','2000-03-25 16:50:29'),
(100006,100001,'冰箱',1,NULL,'2000-03-25 16:52:15','2000-03-25 16:52:15'),
(100007,100001,'电视',1,NULL,'2000-03-25 16:52:26','2000-03-25 16:52:26'),
(100008,100001,'洗衣机',1,NULL,'2000-03-25 16:52:39','2000-03-25 16:52:39'),
(100009,100001,'空调',1,NULL,'2000-03-25 16:52:45','2000-03-25 16:52:45'),
(100010,100001,'电热水器',1,NULL,'2000-03-25 16:52:54','2000-03-25 16:52:54'),
(100011,100002,'电脑',1,NULL,'2000-03-25 16:53:18','2000-03-25 16:53:18'),
(100012,100002,'手机',1,NULL,'2000-03-25 16:53:27','2000-03-25 16:53:27'),
(100013,100002,'平板电脑',1,NULL,'2000-03-25 16:53:35','2000-03-25 16:53:35'),
(100014,100002,'数码相机',1,NULL,'2000-03-25 16:53:56','2000-03-25 16:53:56'),
(100015,100002,'3C配件',1,NULL,'2000-03-25 16:54:07','2000-03-25 16:54:07'),
(100016,100003,'女装',1,NULL,'2000-03-25 16:54:44','2000-03-25 16:54:44'),
(100017,100003,'帽子',1,NULL,'2000-03-25 16:54:51','2000-03-25 16:54:51'),
(100018,100003,'旅行箱',1,NULL,'2000-03-25 16:55:02','2000-03-25 16:55:02'),
(100019,100003,'手提包',1,NULL,'2000-03-25 16:55:09','2000-03-25 16:55:09'),
(100020,100003,'保暖内衣',1,NULL,'2000-03-25 16:55:18','2000-03-25 16:55:18'),
(100021,100004,'零食',1,NULL,'2000-03-25 16:55:30','2000-03-25 16:55:30'),
(100022,100004,'生鲜',1,NULL,'2000-03-25 16:55:37','2000-03-25 16:55:37'),
(100023,100004,'半成品菜',1,NULL,'2000-03-25 16:55:47','2000-03-25 16:55:47'),
(100024,100004,'速冻食品',1,NULL,'2000-03-25 16:55:56','2000-03-25 16:55:56'),
(100025,100004,'进口食品',1,NULL,'2000-03-25 16:56:06','2000-03-25 16:56:06'),
(100026,100005,'白酒',1,NULL,'2000-03-25 16:56:22','2000-03-25 16:56:22'),
(100027,100005,'红酒',1,NULL,'2000-03-25 16:56:30','2000-03-25 16:56:30'),
(100028,100005,'饮料',1,NULL,'2000-03-25 16:56:37','2000-03-25 16:56:37'),
(100029,100005,'调制鸡尾酒',1,NULL,'2000-03-25 16:56:45','2000-03-25 16:56:45'),
(100030,100005,'进口洋酒',1,NULL,'2000-03-25 16:57:05','2000-03-25 16:57:05');
DROP TABLE IF EXISTS `mall_order`;
CREATE TABLE `mall_order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
`order_no` bigint(20) DEFAULT NULL COMMENT '订单号',
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`shipping_id` int(11) DEFAULT NULL,
`payment` decimal(20,2) DEFAULT NULL COMMENT '实际付款金额,单位是元,保留两位小数',
`payment_type` int(4) DEFAULT NULL COMMENT '支付类型,1-在线支付',
`postage` int(10) DEFAULT NULL COMMENT '运费,单位是元',
`status` int(10) DEFAULT NULL COMMENT '订单状态:0-已取消-10-未付款,20-已付款,40-已发货,50-交易成功,60-交易关闭',
`payment_time` datetime DEFAULT NULL COMMENT '支付时间',
`send_time` datetime DEFAULT NULL COMMENT '发货时间',
`end_time` datetime DEFAULT NULL COMMENT '交易完成时间',
`close_time` datetime DEFAULT NULL COMMENT '交易关闭时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `order_no_index` (`order_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `mall_order_item` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单子表id',
`user_id` int(11) DEFAULT NULL,
`order_no` bigint(20) DEFAULT NULL,
`product_id` int(11) DEFAULT NULL COMMENT '商品id',
`product_name` varchar(100) DEFAULT NULL COMMENT '商品名称',
`product_image` varchar(500) DEFAULT NULL COMMENT '商品图片地址',
`current_unit_price` decimal(20,2) DEFAULT NULL COMMENT '生成订单时的商品单价,单位是元,保留两位小数',
`quantity` int(10) DEFAULT NULL COMMENT '商品数量',
`total_price` decimal(20,2) DEFAULT NULL COMMENT '商品总价,单位是元,保留两位小数',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `order_no_index` (`order_no`) USING BTREE,
KEY `order_no_user_id_index` (`user_id`,`order_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `mall_pay_info`;
CREATE TABLE `mall_pay_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`order_no` bigint(20) DEFAULT NULL COMMENT '订单号',
`pay_platform` int(10) DEFAULT NULL COMMENT '支付平台:1-支付宝,2-微信',
`platform_number` varchar(200) DEFAULT NULL COMMENT '支付宝支付流水号',
`platform_status` varchar(20) DEFAULT NULL COMMENT '支付宝支付状态',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `mall_product`;
CREATE TABLE `mall_product` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
`category_id` int(11) NOT NULL COMMENT '分类id,对应mall_category表的主键',
`name` varchar(100) NOT NULL COMMENT '商品名称',
`subtitle` varchar(200) DEFAULT NULL COMMENT '商品副标题',
`main_image` varchar(500) DEFAULT NULL COMMENT '产品主图,url相对地址',
`sub_images` text COMMENT '图片地址,json格式,扩展用',
`detail` text COMMENT '商品详情',
`price` decimal(20,2) NOT NULL COMMENT '价格,单位-元保留两位小数',
`stock` int(11) NOT NULL COMMENT '库存数量',
`status` int(6) DEFAULT '1' COMMENT '商品状态.1-在售 2-下架 3-删除',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `mall_product` (`id`, `category_id`, `name`, `subtitle`, `main_image`, `sub_images`, `detail`, `price`, `stock`, `status`, `create_time`, `update_time`)
VALUES
(26,100002,'Apple iPhone 7 Plus (A1661) 128G 玫瑰金色 移动联通电信4G手机','iPhone 7,现更以红色呈现。','http://img.springboot.cn/241997c4-9e62-4824-b7f0-7425c3c28917.jpeg','241997c4-9e62-4824-b7f0-7425c3c28917.jpeg,b6c56eb0-1748-49a9-98dc-bcc4b9788a54.jpeg,92f17532-1527-4563-aa1d-ed01baa0f7b2.jpeg,3adbe4f7-e374-4533-aa79-cc4a98c529bf.jpeg','<p><img alt=\"10000.jpg\" src=\"http://img.springboot.cn/00bce8d4-e9af-4c8d-b205-e6c75c7e252b.jpg\" width=\"790\" height=\"553\"><br></p><p><img alt=\"20000.jpg\" src=\"http://img.springboot.cn/4a70b4b4-01ee-46af-9468-31e67d0995b8.jpg\" width=\"790\" height=\"525\"><br></p><p><img alt=\"30000.jpg\" src=\"http://img.springboot.cn/0570e033-12d7-49b2-88f3-7a5d84157223.jpg\" width=\"790\" height=\"365\"><br></p><p><img alt=\"40000.jpg\" src=\"http://img.springboot.cn/50515c02-3255-44b9-a829-9e141a28c08a.jpg\" width=\"790\" height=\"525\"><br></p><p><img alt=\"50000.jpg\" src=\"http://img.springboot.cn/c138fc56-5843-4287-a029-91cf3732d034.jpg\" width=\"790\" height=\"525\"><br></p><p><img alt=\"60000.jpg\" src=\"http://img.springboot.cn/c92d1f8a-9827-453f-9d37-b10a3287e894.jpg\" width=\"790\" height=\"525\"><br></p><p><br></p><p><img alt=\"TB24p51hgFkpuFjSspnXXb4qFXa-1776456424.jpg\" src=\"http://img.springboot.cn/bb1511fc-3483-471f-80e5-c7c81fa5e1dd.jpg\" width=\"790\" height=\"375\"><br></p><p><br></p><p><img alt=\"shouhou.jpg\" src=\"http://img.springboot.cn/698e6fbe-97ea-478b-8170-008ad24030f7.jpg\" width=\"750\" height=\"150\"><br></p><p><img alt=\"999.jpg\" src=\"http://img.springboot.cn/ee276fe6-5d79-45aa-8393-ba1d210f9c89.jpg\" width=\"790\" height=\"351\"><br></p>',6999.00,96,1,NULL,'2000-04-13 21:45:41'),
(27,100006,'Midea/美的 BCD-535WKZM(E)冰箱双开门对开门风冷无霜智能电家用','送品牌烤箱,五一大促','http://img.springboot.cn/ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg','ac3e571d-13ce-4fad-89e8-c92c2eccf536.jpeg,4bb02f1c-62d5-48cc-b358-97b05af5740d.jpeg,36bdb49c-72ae-4185-9297-78829b54b566.jpeg','<p><img alt=\"miaoshu.jpg\" src=\"http://img.springboot.cn/9c5c74e6-6615-4aa0-b1fc-c17a1eff6027.jpg\" width=\"790\" height=\"444\"><br></p><p><img alt=\"miaoshu2.jpg\" src=\"http://img.springboot.cn/31dc1a94-f354-48b8-a170-1a1a6de8751b.jpg\" width=\"790\" height=\"1441\"><img alt=\"miaoshu3.jpg\" src=\"http://img.springboot.cn/7862594b-3063-4b52-b7d4-cea980c604e0.jpg\" width=\"790\" height=\"1442\"><img alt=\"miaoshu4.jpg\" src=\"http://img.springboot.cn/9a650563-dc85-44d6-b174-d6960cfb1d6a.jpg\" width=\"790\" height=\"1441\"><br></p>',3299.00,99,1,'2000-04-13 18:51:54','2000-04-13 21:45:41'),
(28,100012,'4+64G送手环/Huawei/华为 nova 手机P9/P10plus青春','NOVA青春版1999元','http://img.springboot.cn/0093f5d3-bdb4-4fb0-bec5-5465dfd26363.jpeg','0093f5d3-bdb4-4fb0-bec5-5465dfd26363.jpeg,13da2172-4445-4eb5-a13f-c5d4ede8458c.jpeg,58d5d4b7-58d4-4948-81b6-2bae4f79bf02.jpeg','<p><img alt=\"11TB2fKK3cl0kpuFjSsziXXa.oVXa_!!1777180618.jpg\" src=\"http://img.springboot.cn/5c2d1c6d-9e09-48ce-bbdb-e833b42ff664.jpg\" width=\"790\" height=\"966\"><img alt=\"22TB2YP3AkEhnpuFjSZFpXXcpuXXa_!!1777180618.jpg\" src=\"http://img.springboot.cn/9a10b877-818f-4a27-b6f7-62887f3fb39d.jpg\" width=\"790\" height=\"1344\"><img alt=\"33TB2Yyshk.hnpuFjSZFpXXcpuXXa_!!1777180618.jpg\" src=\"http://img.springboot.cn/7d7fbd69-a3cb-4efe-8765-423bf8276e3e.jpg\" width=\"790\" height=\"700\"><img alt=\"TB2diyziB8kpuFjSspeXXc7IpXa_!!1777180618.jpg\" src=\"http://img.springboot.cn/1d7160d2-9dba-422f-b2a0-e92847ba6ce9.jpg\" width=\"790\" height=\"393\"><br></p>',1999.00,100,1,'2000-04-13 18:57:18','2000-04-13 21:45:41'),
(29,100008,'Haier/海尔HJ100-1HU1 10公斤滚筒洗衣机全自动带烘干家用大容量 洗烘一体','门店机型 德邦送货','http://img.springboot.cn/173335a4-5dce-4afd-9f18-a10623724c4e.jpeg','173335a4-5dce-4afd-9f18-a10623724c4e.jpeg,42b1b8bc-27c7-4ee1-80ab-753d216a1d49.jpeg,2f1b3de1-1eb1-4c18-8ca2-518934931bec.jpeg','<p><img alt=\"1TB2WLZrcIaK.eBjSspjXXXL.XXa_!!2114960396.jpg\" src=\"http://img.springboot.cn/ffcce953-81bd-463c-acd1-d690b263d6df.jpg\" width=\"790\" height=\"920\"><img alt=\"2TB2zhOFbZCO.eBjSZFzXXaRiVXa_!!2114960396.jpg\" src=\"http://img.springboot.cn/58a7bd25-c3e7-4248-9dba-158ef2a90e70.jpg\" width=\"790\" height=\"1052\"><img alt=\"3TB27mCtb7WM.eBjSZFhXXbdWpXa_!!2114960396.jpg\" src=\"http://img.springboot.cn/2edbe9b3-28be-4a8b-a9c3-82e40703f22f.jpg\" width=\"790\" height=\"820\"><br></p>',4299.00,100,1,'2000-04-13 19:07:47','2000-04-13 21:45:41');
DROP TABLE IF EXISTS `mall_shipping`;
CREATE TABLE `mall_shipping` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`receiver_name` varchar(20) DEFAULT NULL COMMENT '收货姓名',
`receiver_phone` varchar(20) DEFAULT NULL COMMENT '收货固定电话',
`receiver_mobile` varchar(20) DEFAULT NULL COMMENT '收货移动电话',
`receiver_province` varchar(20) DEFAULT NULL COMMENT '省份',
`receiver_city` varchar(20) DEFAULT NULL COMMENT '城市',
`receiver_district` varchar(20) DEFAULT NULL COMMENT '区/县',
`receiver_address` varchar(200) DEFAULT NULL COMMENT '详细地址',
`receiver_zip` varchar(6) DEFAULT NULL COMMENT '邮编',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `mall_shipping` (`id`, `user_id`, `receiver_name`, `receiver_phone`, `receiver_mobile`, `receiver_province`, `receiver_city`, `receiver_district`, `receiver_address`, `receiver_zip`, `create_time`, `update_time`)
VALUES
(4,1,'廖师兄','010','18688888888','北京','北京市','海淀区','中关村慕课网大楼','100000','2000-01-22 14:26:25','2000-01-22 14:26:25');
DROP TABLE IF EXISTS `mall_user`;
CREATE TABLE `mall_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户表id',
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(50) NOT NULL COMMENT '用户密码,MD5加密',
`email` varchar(50) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`question` varchar(100) DEFAULT NULL COMMENT '找回密码问题',
`answer` varchar(100) DEFAULT NULL COMMENT '找回密码答案',
`role` int(4) NOT NULL COMMENT '角色0-管理员,1-普通用户',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '最后一次更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `user_name_unique` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `mall_user` (`id`, `username`, `password`, `email`, `phone`, `question`, `answer`, `role`, `create_time`, `update_time`)
VALUES
(1,'admin','21232F297A57A5A743894A0E4A801FC3','admin@qq.com',NULL,NULL,NULL,0,'2000-08-06 15:12:00','2000-08-06 15:12:00');
-- 新增“支付金额”字段 xxxx年xx月xx日
alter table mall_pay_info add pay_amount decimal(20,2) NOT NULL COMMENT '支付金额' after platform_status;
-- 视频7-13修改mall_pay_info,修改后如下
DROP TABLE IF EXISTS `mall_pay_info`;
CREATE TABLE `mall_pay_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`order_no` bigint(20) NOT NULL COMMENT '订单号',
`pay_platform` int(10) DEFAULT NULL COMMENT '支付平台:1-支付宝,2-微信',
`platform_number` varchar(200) DEFAULT NULL COMMENT '支付流水号',
`platform_status` varchar(20) DEFAULT NULL COMMENT '支付状态',
`pay_amount` decimal(20,2) NOT NULL COMMENT '支付金额',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uqe_order_no` (`order_no`),
UNIQUE KEY `uqe_platform_number` (`platform_number`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 视频8-3修改mall_user表字段结构
alter table mall_user modify create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';
alter table mall_user modify update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次更新时间';