Problems with using multiple single quotes and triple quotes in MySQL concat

When dynamically concatenating strings, we often use character concatenation. I don't understand the quotation marks for concatenation, such as:

1. Why are there three quotation marks in '''+ id +'''? Why is there a plus sign on the left and a plus sign on the right? (Can you explain this to me in detail?)

SQL code

sum(case Leave when '''+ id +''' then DaysNo else 0 end) ['+ name +']'

Normal sentence:

SQL code

SUM(CASE Leave WHEN '01' THEN DaysNo END) AS [CASE Leave],
SUM(CASE Leave WHEN '02' THEN DaysNo END) AS [Sick Leave]

Short answer:

Simply put, single quotes are used to concatenate statements, and three quotes are used to concatenate string variable values.

Because quotation marks have special meanings, they must be written twice to escape them.

2. Is there any difference between single quotes and double quotes for strings?

Simply put, if you are using it normally, use single quotes. If you are inside single quotes, you must use single quotes. Then you need to add an extra single quote (escape) to make it three single quotes.

In standard SQL, string literals are enclosed in single quotes.

If the string itself also includes single quotes, use two single quotes (note, not double quotes; double quotes in the string do not need to be escaped separately).

MySQL's extension to SQL allows the use of both single and double quotes.

Brief summary:

When concat a string, if single quotes are required in the statement, two single quotes can be used instead of one.

mysql> select concat('drop user ''''@', '''', @@hostname, '''');
+----------------------------------------------------+
| concat('drop user ''''@', '''', @@hostname, '''') |
+----------------------------------------------------+
| drop user ''@'bei-f0q5bc2.gemalto.com' |
+----------------------------------------------------+
1 row in set (0.00 sec)

Supplementary extension: MySQL inserts a string with single quotes + double quotes

As shown below:

String needInsertStr= a string between single and double quotes;

String finalStr = needInsertStr.replaceAll("'", "\\\\\'"); //The effect is equivalent to adding an escape symbol before the single quote\
//Same for other special characters long dateMillions = System.currentTimeMillis();

StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("insert into ");
stringBuilder.append(tableName);
stringBuilder.append("(uuid,name,metric_id,service_id,script,deleted,created,updated) ");
stringBuilder.append("values(uuid(),'");
stringBuilder.append(name);
stringBuilder.append("','");
stringBuilder.append(name);
stringBuilder.append("','");
stringBuilder.append(serviceId);
stringBuilder.append("','");
stringBuilder.append(finalStr);
stringBuilder.append("',0,");
stringBuilder.append(dateMillions);
stringBuilder.append(",");
stringBuilder.append(dateMillions);
stringBuilder.append(")");

return stringBuilder.toString();

// The script field is a string with single and double quotes

insert into table(string) values(' "This is a test statement" + \'test single and double quotes\' ')

The above article about using multiple single quotes and triple quotes in MySQL concat is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.