添加字段不改代码的方式 - 一劳永逸

1.先看表结构：

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

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

                       

 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);

参考:  mysql 行转列 列转行 - 平凡希 - 博客园  mysql 行转列 列转行