Mysql 工作时用到的奇葩语句备忘

1 . insert into TABLE(...) select (...)  TABLE2 |  左连接 |  字符窜连接函数 CONCAT |字符串转数字 | 值运算

1insert into tbl_sys_data_dict (acl_code, acl_type, created_by, created_dt, updated_by, updated_dt, 
2version, disabled, order_rank, parent_id, primary_key, primary_value, rich_text_value, secondary_key, 
3secondary_value, id) select NULL, NULL, 'admin', null, NULL, NULL, 0, 
40, 100, d.id,(c.cid+100),c.cname,null, '', '',CONCAT('C',(cid+100)) 
5from region_cn_city c left join  tbl_sys_data_dict d on c.pid = (d.primary_key+0) and d.id like 'P%';
6
7
8
9
10
11insert into tbl_sys_data_dict (acl_code, acl_type, created_by, created_dt, updated_by, updated_dt, 
12version, disabled, order_rank, parent_id, primary_key, primary_value, rich_text_value, secondary_key, 
13secondary_value, id) select NULL, NULL, 'admin', null, NULL, NULL, 0, 
140, 100, d.id,(c.oid+1000),c.oname,null, '', '',CONCAT('O',(oid+1000)) 
15from region_cn_county c left join  tbl_sys_data_dict d on c.cid = (d.primary_key-100) and d.id like 'C%';