To add a field:
ALTER TABLE ' User_movement_log '
Add column Gatewayid int not null default 0 after ' RegionID ' (which field is added later)
The MySQL concat function can concatenate one or more strings, such as
Select Concat (' 10 ');
Select Concat (' 11 ', ' 22 ', ' 33 ');
Results
112233
MySQL's concat function will return NULL if one is null when the string is concatenated.
Select Concat (' One ', ' n ', null);
Results
Null
Concat (B.yearnumber,lpad (b.monthofyear,2,0)) stitching strings.
Lpad (4,2,0) Results: 04, ' 4 ' is 1 bits less than 22 bits, so add 4 to the front of 0
The Lpad (S1,LEN,S2) function fills the string S2 to the beginning of the S1, causes the length of the string to reach Len, and then returns the string S1. If the length of the string S1 is greater than Len, the return value is shortened to the Len character length.
Rpad (4,2,0) Results: 40, ' 4 ' is 1 bits less than 22 bits, so add 4 after 0
The Rpad (S1,LEN,S2) function fills the string S2 to the end of the S1, causes the length of the string to reach Len, and then returns the string S1. If the length of the string S1 is greater than Len, the return value is shortened to the Len character length.
Stored Procedure instance:
DELIMITER $$
Use ' Ucmweb ' $$
DROP PROCEDURE IF EXISTS ' Mmm_bi_data_process_proc ' $$
CREATE PROCEDURE Mmm_bi_data_process_proc ()
BEGIN
UPDATE mmmm_process T
SET data_id= (SELECT ID from mmm_date_time WHERE currdatevar=date_format (sysdate (), '%y%m%d ')-1),
Use_count= (SELECT count (*) from Mmm_contact),
Merge_count= (SELECT count (*) from Mmm_reference_contact)
WHERE T.source_count is not null and t.use_count are null and t.me111_count is null;
end$$
DELIMITER;
MySQL string stitching, stored procedures, (seen from the web)