编辑代码

USE shop;

# 创建用户表
CREATE TABLE sh_user (
 id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '用户id',
 name VARCHAR(100) NOT NULL UNIQUE DEFAULT '' COMMENT '用户名',
 password VARCHAR(255) NOT NULL DEFAULT '' COMMENT '密码',
 salt CHAR(32) NOT NULL DEFAULT '' COMMENT '密码盐',
 email VARCHAR(128) NOT NULL DEFAULT '' COMMENT '邮箱',
 mobile CHAR(11) NOT NULL DEFAULT '' COMMENT '手机号',
 level TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户级别',
 money DECIMAL(10, 2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '金额',
 gender TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别',
 qq VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'QQ',
 is_active TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否激活',
 reg_time TIMESTAMP DEFAULT NULL COMMENT '注册时间',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 update_time TIMESTAMP DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 添加测试数据
INSERT INTO sh_user (id, name, password, salt, money, is_active) VALUES
(1, 'Alex', MD5(CONCAT(MD5('123'), 'salt1')), 'salt1', 1000, 1),
(2, 'Bill', MD5(CONCAT(MD5('123'), 'salt2')), 'salt2', 1000, 1);