Shulou(Shulou.com)05/31 Report--

This article is to share with you about the common splicing sentences of MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Stitching query all users SELECT DISTINCT CONCAT ('User:\', USER,'\ @\', HOST,'\';') AS QUERYFROM mysql.USER;# use the\ escape character 2 when 'appears in the splicing string. Splice DROP tableSELECT CONCAT ('DROP table', TABLE_NAME,';') FROM information_schema. TABLESWHERE TABLE_SCHEMA = 'test';3. Splice kill to connect SELECT concat ('KILL', id,';') FROM information_schema. PROCESSLISTWHERE STATE LIKE 'Creating sort index';4. 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 and creating user statements SELECT CONCAT ('create user\', user,'\'@\', Host,'\ 'IDENTIFIED BY PASSWORD\', authentication_string,'\';) AS CreateUserQueryFROM mysql.`user`Were `User`NOT IN ('root',' mysql.session', 'mysql.sys') # A password string can be executed in other instances to directly create a user with the same password as this instance 6. Export permission script this shell script also uses splicing #! / bin/bash # Function export user privileges pwd=yourpass expgrants () {mysql-B-uplink root'- p$ {pwd}-N $@-e "SELECT CONCAT ('SHOW GRANTS FOR'', user,''@'', host,' ') AS query FROM mysql.user "|\ mysql-ubiquitous root'-p$ {pwd} $@ |\ sed's /\ (GRANT. *\) /\ 1tmp/grants.sql7 expgrants / > / tmp/grants.sqlecho" flush privileges; "> / / tmp/grants.sql7. Look for 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 SELECTtable_ nameFROM information_schema.table_constraints JOIN information_schema.key_column_usage k USING (constraint_name, table_schema) # find a library without key table (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'); thank you for reading! This is the end of this article on "what are the common splicing sentences in MySQL?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.