新零售数据库(4):客户和会员设计

客户和会员表设计


CREATE TABLE t_level(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    `level` VARCHAR(200) NOT NULL COMMENT '等级',
    discount DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '折扣',
    is_deleted BOOLEAN NOT NULL DEFAULT 0 COMMENT '逻辑删除'
    )COMMENT='会员登记表';
CREATE TABLE t_customer(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    username VARCHAR(200) NOT NULL COMMENT '用户名',
    `password` VARCHAR(2000) NOT NULL COMMENT '密码(AES加密)',
    wechat VARCHAR(200) COMMENT '微信',
    tel CHAR(11) COMMENT '手机号',
    level_id INT UNSIGNED COMMENT '会员等级号',
    create_time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '创建时间',
    last_update_time TIMESTAMP NOT NULL DEFAULT NOW()
     COMMENT '最后修改时间',
     is_deleted BOOLEAN NOT NULL DEFAULT 0 COMMENT '逻辑删除',
     INDEX idx_username(username),
     UNIQUE unq_username(username)
)COMMENT='客户表';

客户收货地址

DROP TABLE IF EXISTS `t_customer_address`;
CREATE TABLE `t_customer_address`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_id` int(10) UNSIGNED NOT NULL COMMENT '客户ID',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `tel` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话',
  `address` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地址',
  `prime` tinyint(1) NOT NULL COMMENT '默认地址',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_customer_id`(`customer_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '客户收货地址' ROW_FORMAT = Dynamic;

数据库加密和解密函数

SELECT HEX(AES_ENCRYPT(str,key_str))

SELECT UNHEX(AES_DECRYPT(str,key_str))