-- 准备测试配置表
DROP TABLE IF EXISTS `field_config`;
CREATE TABLE `field_config` (
`text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `field_config` VALUES ('area_id');
INSERT INTO `field_config` VALUES ('parent_id');
INSERT INTO `field_config` VALUES ('area_name');
INSERT INTO `field_config` VALUES ('area_type');
-- 准备测试数据表
DROP TABLE IF EXISTS `ks_areas`;
CREATE TABLE `ks_areas` (
`area_id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '地区id',
`parent_id` smallint(6) UNSIGNED NOT NULL DEFAULT 0 COMMENT '地区父id',
`area_name` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '地区名称',
`area_type` tinyint(1) NOT NULL DEFAULT 2 COMMENT '地区类型 0:country,1:province,2:city,3:district',
PRIMARY KEY (`area_id`) USING BTREE,
INDEX `parent_id`(`parent_id`) USING BTREE,
INDEX `area_type`(`area_type`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3429 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `ks_areas` VALUES (1, 0, '中国', 0);
INSERT INTO `ks_areas` VALUES (2, 1, '北京', 1);
INSERT INTO `ks_areas` VALUES (3, 1, '安徽', 1);
INSERT INTO `ks_areas` VALUES (4, 1, '福建', 1);
INSERT INTO `ks_areas` VALUES (5, 1, '甘肃', 1);
INSERT INTO `ks_areas` VALUES (6, 1, '广东', 1);
INSERT INTO `ks_areas` VALUES (7, 1, '广西', 1);
-- 编写查询语句
SET @var = '1';
SET @var1 = (SELECT CONCAT('JSON_OBJECT\(',GROUP_CONCAT(CONCAT('\'',text,'\',',text)),'\)') from field_config);
SET @strsql = concat('SELECT ',@var1,' from ks_areas where area_id >', @var);
PREPARE stmt FROM @strsql;
EXECUTE stmt;
返回结果为:
-- 新增表字段之后处理方式:
-- 第一步新增表字段
ALTER TABLE ks_areas ADD test varchar(20) COMMENT '测试字段';
-- 第二步新增配置表字段
INSERT into field_config VALUE('test');
-- 第三步 重新执行查询语句即可。
返回结果为:
come over!