我要评论准备测试配置表
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');第三步 重新执行查询语句即可。
返回结果为：
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论