Shulou(Shulou.com)06/01 Report--

Editor to share with you what the commonly used splicing sentences in MySQL are, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

In MySQL, the CONCAT () function is used to concatenate multiple strings into a string. Using this function, we can piece together the sql that can not be obtained in the original step, which may be much more convenient in our work. Here are some common scenarios.

Note: the lower version of 5.7 may be slightly different.

1. Stitching query all users

SELECT DISTINCT CONCAT ('User:\', USER,'\'@\', HOST,'\';') AS QUERYFROM mysql.USER;# uses the\ escape character when 'appears in the splicing string

two。 Splicing DROP table

SELECT CONCAT ('DROP table', TABLE_NAME,';') FROM information_schema. TABLESWHERE TABLE_SCHEMA = 'test'

3. Splicing kill connection

SELECT concat ('KILL', id,';') FROM information_schema. PROCESSLISTWHERE STATE LIKE 'Creating sort index'

4. Stitching to create database statements

SELECT CONCAT ('create database','`', SCHEMA_NAME,'`, 'DEFAULT CHARACTER SET', DEFAULT_CHARACTER_SET_NAME,';') AS CreateDatabaseQueryFROM information_schema.SCHEMATAWHERE SCHEMA_NAME NOT IN ('information_schema',' performance_schema', 'mysql',' sys')

5. Stitching statements to create a user

SELECT CONCAT ('create user\', user,'\'@\', Host,'\ 'IDENTIFIED BY PASSWORD\', authentication_string,'\';') AS CreateUserQueryFROM mysql.`user`Were `User`NOT IN ('root',' mysql.session', 'mysql.sys') # there is a password string. If executed in other instances, you can directly create users with the same password as this instance.

6. Splicing is also used in the export permission script, the shell script.

#! / bin/bash # Function export user privileges pwd=yourpass expgrants () {mysql-B-upright root'-p$ {pwd}-N $@-e "SELECT CONCAT ('SHOW GRANTS FOR'', user,''@'', host,'';') AS query FROM mysql.user" |\ mysql-upright root'-p$ {pwd} $@ |\ sed's /\ (GRANT. *\) /\ political / S / ^\ (Grants for. *\) /--\ 1 /; /-- / {xtreptx;}'} expgrants > / tmp/grants.sqlecho "flush privileges;" > > / tmp/grants.sql

7. Find table fragments

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS, concat (round (t.DATA_LENGTH / 1024 / 1024, 2),'M') AS size, t.INDEX_LENGTH, concat (round (t.DATA_FREE / 1024 / 1024, 2),'M') AS datafreeFROM information_schema.tables tWHERE t.TABLE_SCHEMA = 'test' order by DATA_LENGTH desc

8. Find the table without primary key and share it if you don't need splicing.

# find a library without primary key table SELECTtable_schema,table_nameFROM information_schema.TABLESWHERE table_schema = 'test'AND TABLE_NAME NOT IN (SELECTtable_ nameFROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING (constraint_name, table_schema,table_name) WHERE t.constraint_type =' PRIMARY KEY' AND t.table_schema = 'test') # find no primary key table SELECT t1.table_schema, t1.table_nameFROM information_schema except system library. TABLES t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS T2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY') WHERE t2.table_name IS NULLAND t1.TABLE_SCHEMA NOT IN (' information_schema', 'performance_schema',' mysql', 'sys'); the above is all the content of the article "what are the splicing sentences commonly used in MySQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.