Splicing data after update data (suitable for historical record redundancy)

There is a need for a job to record all its value-added service operation information

It is impossible to delete the original data when recording, so I thought, is there a syntax for MYSQL, instead of selecting the original data and then updating, but directly splicing in update?

Fortunately, I have found this syntax. Thanks to the search engine, I am also grateful to my predecessors who have shared this syntax on the Internet.

Syntax: CONCAT (IFNULL (field name, ''),'', and 'strings to be appended ')

Complete is the update table set field name = CONCAT (IFNULL (field name, ''),'', 'strings to be appended ')

In this case, the field can be null.

If not, write is omitted.

Below is the reprinted

---------------------------------------------------

1,Concat()Function
1.1 MySQL concat function can connect one or more strings,For example
Mysql>Select concat('10');
+ -------------- +
|Concat('10') |
+ -------------- +
|10|
+ -------------- +
1 row in set(0.00 sec)

Mysql>Select concat('11','22','33');
+ ------------------------ +
|Concat('11','22','33') |
+ ------------------------ +
|112233|
+ ------------------------ +

1 row in set(0.00 sec)

The concat function of Oracle can only connect two strings

SQL>Select concat('11','22')From dual;

1.2 When the concat function of MySQL connects strings, if one of them is NULL,The return value is NULL.

Mysql>Select concat('11','22',Null);
+ ------------------------ +
|Concat('11','22',Null) |
+ ------------------------ +
|NULL|
+ ------------------------ +
1 row in set(0.00 sec)

When Oracle concat functions are connected, as long as one string is not NULL,No NULL is returned.

SQL>Select concat('11',NULL)From dual;
CONCAT
--
11

2. concat_ws()Function,Concat with separator,Strings with Separators
For example, separate the strings with commas (,).
Mysql>Select concat_ws(',','11','22','33');

+ ------------------------------- +
|Concat_ws(',','11','22','33') |
+ ------------------------------- +
|11,22,33|
+ ------------------------------- +
1 row in set(0.00 sec)

Unlike concat,When the concat_ws function is executed,NULL is not returned because of NULL.
Mysql>Select concat_ws(',','11','22',NULL);
+ ------------------------------- +
|Concat_ws(',','11','22',NULL) |
+ ------------------------------- +
|11,22|
+ ------------------------------- +
1 row in set(0.00 sec)

NOTE: If NULL is found in the connected field,Select concat_ws('Separator', IFNULL (FIELD, 'Str '), IFNULL(FIELD, 'str'), IFNULL (NULL, 'str ')); Here, the IFNULL function can be used for processing. If the field is NULL, It will be replaced with other characters, so as to avoid string connection errors.

Reference: -- modify the update statement for the RFQ service reminder
Insert into T_SITECONFIG (ID, VERSION, IDENTIFIER, VALUE)
Select a. ID as ID, 0 as VERSION, a. MODULETAG as IDENTIFIER,
Concat_ws ('; zf91c8fm;', IFNULL (. ISEMAIL, ''), IFNULL (. EMAIL, ''), IFNULL (. EMAILTEMPLATEID, ''), IFNULL (. EMAILREPLYTEMPLATEID, ''), IFNULL (. ISMOBILE, ''), IFNULL (. MOBILE, ''), IFNULL (. MOBILETEMPLATEID, ''), IFNULL (. assumereplytemplateid ,''))
As VALUE from T_BIZREMIND a where a. MODULETAG = 'akprice ';

3. group_concat()It can be used for Row-to-column conversion.,Oracle does not have such a function

The complete syntax is as follows:
Group_concat([DISTINCT]Fields to be connected[Order BY ASC/DESC sorting Field] [Separator'Delimiter'])
Example
Mysql>Select*From aa;

+ ------ +
|Id|Name|
+ ------ +
|1|10|
|1|20|
|1|20|
|2|20|
|3|200|
|3|500|
+ ------ +
6 rows in set(0.00 sec)
3.1 group by id and print the value of the name field in one row, separated by commas (,)(Default)
Mysql>Select id,Group_concat(Name)From aa group by id;
+ ------ + -------------------- +
|Id|Group_concat(Name) |
+ ------ + -------------------- +
|1|10,20,20|
|2|20|
|3|200,500|
+ ------ + -------------------- +

3 rows in set(0.00 sec)

3.2 group by id, print the value of name field in one row, separated by semicolons
Mysql>Select id,Group_concat(Name separator';')From aa group by id;
+ ------ + ------------------------------------ +
|Id|Group_concat(Name separator';') |
+ ------ + ------------------------------------ +
|1|10;20;20|
|2|20|
|3|200;500|
+ ------ + ------------------------------------ +

3 rows in set(0.00 sec)

3.3 group by id and print the value of the redundant name field in one row, separated by commas

Mysql>Select id,Group_concat(Distinct name)From aa group by id;

+ ------ + ----------------------------- +
|Id|Group_concat(Distinct name) |
+ ------ + ----------------------------- +
|1|10,20|
|2|20|
|3|200,500|
+ ------ + ----------------------------- +

3 rows in set(0.00 sec)

3.4 group by id and print the value of the name field in one row, separated by commas (,),Sort by name in reverse order

Mysql>Select id,Group_concat(Name order by name desc)From aa group by id;

+ ------ + --------------------------------------- +
|Id|Group_concat(Name order by name desc) |
+ ------ + --------------------------------------- +
|1|20,20,10|
|2|20|
|3|500,200|
+ ------ + --------------------------------------- +

3 rows in set(0.00 sec)

4. repeat()Function used to copy strings,As follows:'AB'Indicates the string to be copied, and 2 indicates the number of copies.

Mysql>Select repeat('AB',2);

+ ---------------- +
|Repeat('AB',2) |
+ ---------------- +
|Abab|
+ ---------------- +

1 row in set(0.00 sec)

Ru
Mysql>Select repeat('A',2);

+ --------------- +
|Repeat('A',2) |
+ --------------- +
|Aa|
+ --------------- +
1 row in set(0.00 sec)