MySQL Concat Usage Example

Application of Concat with Information_schema

1 The general usage of concat is mainly used for splicing

Example:

The EXECUTE statement SELECT CONCAT (' M ', ' y ', ' S ', ' Q ', ' L ') can achieve the following effect

2 When writing SQL backup statements, assume that you want to export three tables

Before we write the backup statement to write three of the three tables to be exported, respectively, the statements are

- - >/tmp/-->/tmp/--  >/tmp/World_countrylanguage.sql

View Code

But this is a specific statement, if there are 3000 tables under the library, 30000 of them; This is not the best way to export, so we can associate concat with INFORMATION_SCHEMA.

 select  CONCAT ( " mysqldump-uroot-p123  , Table_schema,"  , Table_name,"   >/tmp/ , Table_schema,"  _  , Table_name,"  .sql   ") from   Information_schema.tables  where  table_schema=   " world  ; 

View Code

Please note some details of the interim:

A space in the middle of the quotation mark indicates a space, table_schema the name of the database.

TABLE_NAME represents the table name; These properties can be found in Information_schema

This is the initial writing, and the performance is as follows

The last way to deal with it: The statement is written out, to achieve our desired effect; if there are other tables in this library in the future, they will be added to the CONCAT output statement.

The above is not the best effect

We'd better export the statement to the specified file

The first execution will be error;

This is handled as follows: New statements under/ETC/MY.CNF specify a secure Path

Secure-file-priv=/tmp and restart the database/etc/init.d/mysqld restart

Re-execute statement no longer error

See the results at the terminal as follows:

The script can be executed directly with SH and the 4 tables will be backed up.

MySQL Concat Usage Example