上线后添加字段的表设计-添加属性表，属性值表

傷城~ 2022-09-10 14:28 170阅读 0赞

### **1.先看表结构：** ###

(表结构DDL丶数据插入DML在末尾)

从左到右依次是: user用户表,  field字段表,  field\_value字段值  表

![20210909141053947.png][]        ![2021090914105624.png][]        ![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6IOh6JCn5b6S_size_14_color_FFFFFF_t_70_g_se_x_16][]

###  2.表作用解析 ###

**user ** 用户表,对应实际生产过程中的业务表

**field ** 字段表,user表新增的字段存放在这里

**field\_value ** 字段值表,存放user表在file字段表中对应的值

### 3.如何查询 ###

-- 关联用户和属性表,取出完整数据表(可以建成视图,需要用直接查)
    SELECT u.*,
    -- 用if把行数据转为列数据(此处用case when也可以)
    IF ( ue.field_name = 'phone', field_value, 0 ) AS `phone`,
    IF ( ue.field_name = 'level', field_value, 0 ) AS `level` 
    FROM `user` u LEFT JOIN 
    	(
    	  -- 关联属性和属性值表,取出所有数据
    		SELECT fv.user_id, f.field_name, fv.field_value 
    		FROM field_value fv
    		LEFT JOIN field f ON fv.field_id = f.id 
    	) ue ON u.id = ue.user_id 
    GROUP BY u.id ;

-- 以下是DDL丶DDM

-- Table structure for field
    DROP TABLE IF EXISTS `field`;
    CREATE TABLE `field`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `field_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    -- Records of field
    INSERT INTO `field` VALUES (1, 'phone');
    INSERT INTO `field` VALUES (2, 'level');
    
    -- Table structure for field_value
    DROP TABLE IF EXISTS `field_value`;
    CREATE TABLE `field_value`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` int(11) NULL DEFAULT NULL,
      `field_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `field_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    -- Records of field_value
    INSERT INTO `field_value` VALUES (1, 1, '1', '123');
    INSERT INTO `field_value` VALUES (2, 1, '2', '1');
    INSERT INTO `field_value` VALUES (3, 2, '1', '456');
    INSERT INTO `field_value` VALUES (4, 2, '2', '10');
    
    -- Table structure for user
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `sex` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `age` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    -- Records of user
    INSERT INTO `user` VALUES (1, 'even', 'F', 18);
    INSERT INTO `user` VALUES (2, 'fox', 'M', 18);

参考:  [https://www.cnblogs.com/xiaoxi/p/7151433.html][https_www.cnblogs.com_xiaoxi_p_7151433.html]  [mysql 行转列 列转行][https_www.cnblogs.com_xiaoxi_p_7151433.html]

[20210909141053947.png]: https://img-blog.csdnimg.cn/20210909141053947.png
[2021090914105624.png]: https://img-blog.csdnimg.cn/2021090914105624.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6IOh6JCn5b6S_size_14_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/d592d54a304449eb9da32582d7be0da1.png
[https_www.cnblogs.com_xiaoxi_p_7151433.html]: https://www.cnblogs.com/xiaoxi/p/7151433.html