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

How to use sed to take out the table structure of a table from the mysqldump complete document, many beginners are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

How to get the table structure content in the mysqldump file with sed

Suppose the contents of the original file are as follows:

[root@demo-init ~] # cat db1.sql

-- MySQL dump 10.13 Distrib 5.7.29, for linux-glibc2.12 (x86 / 64)

--

-- Host: localhost Database: db1

-

-- Server version 5.7.29-log

/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /

/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /

/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /

/ * 40101 SET NAMES utf8 * /

/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /

/ * 40103 SET TIME_ZONE='+00:00' * /

/ * 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /

/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /

/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /

/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /

--

-- Table structure for table `t1`

--

DROP TABLE IF EXISTS `t1`

/ *! 40101 SET @ saved_cs_client = @ @ character_set_client * /

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `t1` (

`id`int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/ * 40101 SET character_set_client = @ saved_cs_client * /

--

-- Dumping data for table `t1`

--

LOCK TABLES `t1` WRITE

/ *! 40000 ALTER TABLE `t1` DISABLE KEYS * /

INSERT INTO `t1` VALUES (3306)

/ *! 40000 ALTER TABLE `t1` ENABLE KEYS * /

UNLOCK TABLES

/ * 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /

/ * 40101 SET SQL_MODE=@OLD_SQL_MODE * /

/ * 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS * /

/ * 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS * /

/ * 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * /

/ * 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS * /

/ * 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION * /

/ * 40111 SET SQL_NOTES=@OLD_SQL_NOTES * /

-- Dump completed on 2020-04-12 15:43:52

Use sed to extract the table structure:

[root@demo-init ~] # sed-e'/. / {Henton d}'- e'x scarcity create TABLE `t1` /! d 'db1.sql

DROP TABLE IF EXISTS `t1`

/ *! 40101 SET @ saved_cs_client = @ @ character_set_client * /

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `t1` (

`id`int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/ * 40101 SET character_set_client = @ saved_cs_client * /

This is very good, the sql statement of create table T1 is completely fetched. How does it do that.

First, explain a few options for sed.

The H command appends the contents of the current mode space to the holding space

The d command deletes the current mode space content (no longer passed to standard output), discards the later command, reads the new content and re-executes sed (red is very important)

X: command to interchange the contents of the pattern space with the contents of the hold space

The-e option performs multiple processing actions, which is equivalent to

With the above understanding, you can explain the following command.

Sed-e'/. / {Hutterd}'- e'xternal create TABLE `t1` /! d'db1.sql

/. / means regular matching. It refers to any single character, so this means to match a non-blank line, that is, a line with content.

{} this is a collection of commands

Because sed processes text line by line, at the beginning, the first line is-- MySQL dump 10.13. Put it in the pattern space, then use the H command to put-- MySQL dump 10.13. Append the hold space, and the contents of the hold space (default deposit) will become\ n MySQL dump 10.13. Then execute the d command to change the contents of the pattern space-- MySQL dump 10.13. Delete it, and no longer execute the following command-e'xposition create TABLE `t1` /! d'.

Read the second line, that is, put it into the pattern space, and then execute the command H to append the contents of the pattern space to the hold space, and then the content of the hold space becomes\ nmura-MySQL dump 10.13..\ nMuk, and then execute command d to erase the contents of the pattern space.

Then repeat the above step until the sixth line, which is a blank line, does not match the /. / this regular expression, so the command after the first-e is not executed, but the command after the second-e'x position TABLE `t1` /! d 'starts to execute. Execute the x command first, which means to interchange the content in the pattern space with the content in keeping empty love you, that is, to exchange the empty content in the pattern space with the MySQL dump 10.13 in the holding space.\ n -\ n-Table structure. Exchange, so that the pattern space has content, and the content of the pattern space executes the command / CREATE TABLE `t1` /! d. Because the content in the pattern space does not conform to the regular expression / CREATE TABLE `t1` /, it is necessary to clear the content in the pattern space with the d command.

So continue to execute, until encounter create table `t1` group (db1.sql each set of content is separated by blank lines), sed will keep the space create table T1 context of the contents of the complete output to the screen

In fact, the key to this series of sed commands is the d command, which is to delete the current mode space content (no longer sent to standard output), and give up the later command, read the new content and re-execute sed. After reading this sentence, I finally figured out how this string of sed commands get the create table T1 context result.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.