The original table:
Id | name | date | record_id
1 | 20180101 | | A null
2 B | | 20180102 | null
3 C | | 20180103 | null
Update table:
Id | name | date | record_id
1 | 20180101 | | A A - 20180101-001
2 | | 20180102 | B - 20180102 - B - 002
3 C | | 20180103 | C - 20180103-003
Among them, the id is listed on the primary key, the purpose is joining together the name - the date - id to the new fields record_id,
At the same time, every time I fill in the new name and the date, such as insert name='D', the date='20180104', get:
4 D | | 20180104 | D - 20180104-004
Give advice or comments, thanks,
CodePudding user response:
The update tablename set record_id=concat (name, "-", the date. "-", replace (STR (id, 3), ' ', '0'));
Full table to update the table called table tablename,
concat (str1, str2,... ) to connect multiple strings;
replace (STR (id, 3), ' ', '0') field ids first converted into three types of STR characters, then the '(null) into 0, realize digital fixed length string, insufficient length 0 and 1 to 001,