mysql 技巧语句

mysql 常用

对存量数据的修改

update cp_info set use_time = first_use_time; //给use_time赋上first_use_time的值

在A表插入B表的查询结果

insert into A(application_num,`function`,status ) select application_num,function,status from B;

修改P表中的某列值为A表中查询到的

update project p set project_name = (select app_name  from  app a where p.package_name = a.package_name )；

数据库中存的function_id数据为"1,2,6",将该字段按,分割成多个字段

select id,application_num,function_ids,substring_index(substring_index(function_ids, ',', 1), ',', 1) as function1,substring_index(substring_index(function_ids, ',', 2), ',', -1) as function2 ,status from application_info

批量替换某字段中的某一个值

select id,currency_accept  from table_name where currency_accept like '%"extraFee": "0"%'

update table_name  set currency_accept = replace(currency_accept,'"extraFee": "0"','"extraFee": 0') where currency_accept like '%"extraFee": "0"%';