Summary of MySQL's commonly used concatenation statements

Summary of MySQL's commonly used concatenation statements

Preface: In MySQL, the CONCAT() function is used to concatenate multiple strings into one string. Using this function, we can splice out the SQL that could not be obtained in one step. It may be much more convenient in work. The following mainly introduces several commonly used scenarios.

Note: Applicable to version 5.7 and lower versions may be slightly different.

1. Join and query all users

SELECT DISTINCT
  CONCAT(
    'User: \'',
    USER,
    '\'@\'',
    HOST,
    '\';'
  ) AS QUERY
FROM
  mysql.USER;
# When ' appears in the concatenated string, you need to use the \ escape character

2. Join DROP tables

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

3. Splice kill connection

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

4. Splice the statements to create a database

SELECT
  CONCAT(
    'create database ',
    '`',
  SCHEMA_NAME,
  '`',
  ' DEFAULT CHARACTER SET ',
  DEFAULT_CHARACTER_SET_NAME,
    ';'
  ) AS CreateDatabaseQuery
FROM
  information_schema.SCHEMATA
WHERE
  SCHEMA_NAME NOT IN (
    'information_schema',
    'performance_schema',
    'mysql',
    'sys'
  );

5. Concatenate statements to create users

SELECT
  CONCAT(
    'create user \'',
  user,
  '\'@\'',
  Host,
  '\''
  ' IDENTIFIED BY PASSWORD \'',
  authentication_string,
    '\';'
  ) AS CreateUserQuery
FROM
  mysql.`user`
WHERE
  `User` NOT IN (
    'root',
    'mysql.session',
    'mysql.sys'
  );
# There is a password string. You can directly create a user with the same password as this instance by executing it in other instances

6. Export permission script This shell script also uses splicing

#!/bin/bash 
#Function export user privileges 

pwd=yourpass 
expgrants() 
{ 
 mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
 mysql -u'root' -p${pwd} $@ | \
 sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}' 
} 

expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql

7. Lookup table fragmentation

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 datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = 'test' order by DATA_LENGTH desc;

8. Find the table without primary key. This is not used for splicing, so share it.

#Find a table without a primary key in a certain librarySELECT
table_schema,
table_name
FROM
  information_schema.TABLES
WHERE
  table_schema = 'test'
AND TABLE_NAME NOT IN (
  SELECT
    table_name
  FROM
    information_schema.table_constraints
  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'
);

#Search for tables without primary keys except system librariesSELECT
  t1.table_schema,
  t1.table_name
FROM
  information_schema. TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
  t2.table_name IS NULL
AND t1.TABLE_SCHEMA NOT IN (
  'information_schema',
  'performance_schema',
  'mysql',
  'sys'
) ;

The above is the detailed content of the commonly used concatenation statements in MySQL. For more information about MySQL concatenation statements, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL uses show status to view MySQL server status information
  • How to get table information in MySQL show table status
  • Summary of commonly used SQL statements for creating MySQL tables
  • Record a pitfall of MySQL update statement update
  • Navicat Premium operates MySQL database (executes sql statements)
  • The difference between two MySQL delete user statements (delete user and drop user)
  • Summary of MySQL database like statement wildcard fuzzy query
  • Summary of methods for writing judgment statements in MySQL
  • MySQL data duplicate checking and deduplication implementation statements
  • Use of MySQL SHOW STATUS statement

<<:  Linux uses shell scripts to regularly delete historical log files

>>:  A simple and in-depth study of async and await in JavaScript

Recommend

PHP scheduled backup MySQL and mysqldump syntax parameters detailed

First, let's introduce several common operati...

Detailed introduction to nobody user and nologin in Unix/Linux system

What is the nobody user in Unix/Linux systems? 1....

Font Treasure House 50 exquisite free English font resources Part 1

Designers have their own font library, which allo...

abbr mark and acronym mark

The <abbr> and <acronym> tags represen...

Detailed explanation of how to install MySQL on Alibaba Cloud

As a lightweight open source database, MySQL is w...

How to add, delete and modify columns in MySQL database

This article uses an example to describe how to a...

Detailed explanation of meta tags (the role of meta tags)

No matter how wonderful your personal website is,...

Use nexus as a private library to proxy docker to upload and download images

1. Nexus configuration 1. Create a docker proxy U...

Native JS to achieve book flipping effects

This article shares with you a book flipping effe...

CSS3 implements the sample code of NES game console

Achieve resultsImplementation Code html <input...

Detailed explanation of CSS text decoration text-decoration &amp; text-emphasis

In CSS, text is one of the most common things we ...

Solve the problem that await does not work in forEach

1. Introduction A few days ago, I encountered a p...

Centos7.5 installs mysql5.7.24 binary package deployment

1. Environmental preparation: Operating system: C...

Detailed analysis of MySQL optimization of like and = performance

introduction Most people who have used databases ...

A brief analysis of the knowledge points of exporting and importing MySQL data

Often, we may need to export local database data ...