concat()字符串连接

  • CONCAT()函数用于将多个字符串连接成一个字符串

CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。或许有一个或多个参数。 如果所有参数均为非二进制字符串,则结果为非二进制字符串。 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast。SELECT CONCAT(CAST(int_col AS CHAR), char_col)

1
2
3
4
5
6
MariaDB [test]> select concat("AB","C");
+------------------+
| concat("AB","C") |
+------------------+
| ABC |
+------------------+
  • CONCAT_WS(separator,str1,str2,…)
    CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
1
2
3
4
5
6
MariaDB [test]> select concat_ws(",","AB","C");
+-------------------------+
| concat_ws(",","AB","C") |
+-------------------------+
| AB,C |
+-------------------------+

prepare()预处理

  • PREPARE:准备一条SQL语句,并分配给这条SQL语句一个名字供之后调用

  • EXECUTE :执行命令

  • DEALLOCATE PREPARE:释放命令

    在 SQL 语句中,我们使用了问号 (?),在此我们可以将问号替换为整型,字符串,双精度浮点型和布尔值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
MariaDB [test]> PREPARE pr1 FROM 'SELECT ?+?';
Query OK, 0 rows affected (0.000 sec)
Statement prepared

MariaDB [test]> SET @a=1, @b=2;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> EXECUTE pr1 USING @a,@b;
+------+
| ?+? |
+------+
| 3 |
+------+
1 row in set (0.000 sec)

MariaDB [test]> EXECUTE pr1 USING 2,3;
+------+
| ?+? |
+------+
| 5 |
+------+
1 row in set (0.000 sec)

MariaDB [test]> DEALLOCATE PREPARE pr1;
Query OK, 0 rows affected (0.000 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
mysql> CREATE TABLE a (a int);
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO a SELECT 1;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> INSERT INTO a SELECT 2;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> INSERT INTO a SELECT 3;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SET @select_test = CONCAT('SELECT * FROM ', @table_name);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @table_name = 'a';
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE pr2 FROM @select_test;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE pr2 ;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

mysql> DROP PREPARE pr2; -- 此处DROP可以替代DEALLOCATE
Query OK, 0 rows affected (0.00 sec)