mysql> select version();
+------------+
| version() |
+------------+
| 5.7.21-log |
+------------+
1 row in set (18.09 sec)
触发器
CREATE TRIGGER `T` BEFORE INSERT ON `srm_source_list_line_copy1`
FOR EACH ROW begin
set new.srm_source_list_num=concat('IMH',lpad(((SELECT substring(srm_source_list_num,4,10) from srm_source_list_line_copy1 where srm_source_list_num=(select srm_source_list_num from srm_source_list_line_copy1 order by srm_source_list_num desc limit 1))+1),10,0));
end;
when then
UPDATE srm_purchase_info_record_line_copy1 SET
created_date = NOW(),last_edited_date = NOW(),tenant_code = '10000028',
state = 'DRAFT',
material_category_code = '08',
material_category_id = '2FE07A4C-9190-11EA-A7EB-0242C0A84404',
material_category_name ='生产性物资',
purchase_info_remark = '历史数据',
id = UUID(),
project_category_code = case record_type_code when '0' Then '0' when '2' Then 'K' when '3' Then 'L' end;
迁移表字段数据(列名数一样)
INSERT INTO user2(`u_id`,`name`) SELECT `id`,`name` FROM user;
length concat
UPDATE srm_source_list_line SET
srm_source_list_item_num = CONCAT('000',srm_source_list_item_num,0) WHERE LENGTH(srm_source_list_item_num) = 1;
UPDATE srm_source_list_line SET
srm_source_list_item_num = CONCAT('00',srm_source_list_item_num,0) WHERE LENGTH(srm_source_list_item_num) = 2;
索引
ALTER table baf_sap_material_group_map_purchase_group ADD INDEX business_unit(business_unit);
函数
MID()函数 - 用于得到一个字符串的一部分 这个函数被 MySQL 支持,但不被 MS SQL Server 和 Oracle 支持。在 SQL Server, Oracle 数据库中,我们可以使用 SQL SUBSTRING 函数或者 SQL SUBSTR 函数作为替代。