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

Editor to share with you how to use the help command in MySQL, 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!

Where did the 01help statement information come from?

MySQL Server provides four tables for saving server-side help information (using help syntax to view help information), which are located in the mysql system dictionary library. The help statement takes data from these tables and returns them to the client, as follows:

Help_category: information about help topic categories

Help_keyword: keyword information related to help topics

Help_relation: mapping between help keyword information and topic information

Help_topic: details of help topics

When was the 02help statement information generated?

These tables are created by loading the share/fill_help_tables.sql file when the database is initialized, and if MySQL is installed on Unix using a binary or source code distribution, the file is imported directly to initialize the contents of the help table when initializing the data directory. For RPM distributions on Linux or binary distributions on Windows, the contents of the help table are initialized as part of the MySQL installation process.

If you upgrade MySQL with a binary distribution, the help table will not be upgraded automatically, but you can manually upgrade (manually load the share/fill_help_tables.sql file), such as: shell > mysql-u root mysql select * from help_category +-+-- + | help_category_id | name | | parent_category_id | url | +-+-- + | 1 | Geographic | | | 0 | | 2 | Polygon properties | 35 | |. | 39 | Functions | 36 | 40 | Data Definition | 36 | | +-+-- +- +-+ 40 rows in set (0.00 sec)

Table field meaning

Help_category_id: record ID of the help topic name or subcategory name in the table

Name: help topic category name or word category name

Parent_category_id: record ID of the parent topic category name in the table. Some topic categories have subtopic categories. For example, most topic categories are actually subcategories of the Contents category (and are also the top-level category and also the first-level parent category), and some are subcategories of the Geographic Features category (secondary parent category), and some are subcategories of Functions (secondary parent category).

Url: the link address corresponding to the official manual of MySQL

(2) help_keyword

This table provides query keyword string information related to help topics, as follows:

Root@localhost: mysql 01:12:07 > select * from help_keyword limit 5 +-+-+ | help_keyword_id | name | +-+-+ | 681 | (JSON | | 486 |-> | | 669 | 521 | ACCOUNT | +-+-+ 5 rows in set (0.00 sec)

Table field meaning

Help_keyword_id: the help keyword name records the corresponding ID in the table

Name: help keyword string

(3) help_relation

This table provides a mapping between query help keyword information and topic details for associating query help_keyword with help_topic tables, as follows:

Root@localhost: mysql 01:13:09 > select * from help_relation limit 5 +-+-+ | help_topic_id | help_keyword_id | +-+-+ | 0 | 0 | 535 | 0 | 294 | | 1 | | 277 | 2 | | 2 | 3 | +-+-+ 5 rows in set (0.00 sec) |

Table field meaning

Help_topic_id: help topic details ID, which is equal to the help_topic_id in the help_ topic table

Help_keyword_id: help topic keyword information ID, with an ID value equal to help_keyword_id in the help_ keyword table

(4) help_topic

This table provides the details (detailed help information) of the keywords given in the query help topic, as follows:

Root@localhost: mysql 01:13:31 > select * from help_topic limit 1\ G * * 1. Row * * help_topic_id: 0 name: JOINhelp_category_id: 28 description: MySQL supports the following JOIN syntaxes for the table_referencespart of SELECT statements and multiple-table DELETE and UPDATEstatements:table_references:escaped_table_reference [ Escaped_table_reference]. Escaped_table_reference:table_reference | {OJ table_reference}. Url: http://dev.mysql.com/doc/refman/5.7/en/join.html1 row in set (0.00 sec)

Table field meaning

Help_topic_id: the ID for the help topic details in the table record

Name: the keyword name given by the help topic, which is equal to the name field value in the help_ keyword table

Help_category_id: help topic category ID, which is equal to the value of the help_category_id field in the help_ topics table

Description: details of the help topic (here is what we usually want to see when we query for help, such as the syntax and notes that tell us how to use the so-and-so statement)

Example: sample information for help topics (here is an example of how the so-and-so statement is used)

Url: this help topic corresponds to the URL link address in MySQL's official online manual

Example of 04help statement usage

As we mentioned earlier, the help syntax supports matching queries for three patterns. So, going back to the question we threw at the beginning of the article, we can't remember the specific spelling of a sentence, and we can only vaguely remember a few letters, or clearly know what help information we want to look up, but we don't know what keyword to use to query the help information (for example, we want to see the SQL statement that parses relaylog). What should I do at this time?

(1) what if I only remember a few letters

The help information provided by MySQL can actually be queried directly with a topic keyword without specifying a topic name. If you record a few letters in a SQL clause keyword, you can use these letters several times, as follows:

Root@localhost: performance_schema 10:43:40 > help relay # try the first Nothing foundPlease try to run 'help contents' for a list of all accessible topicsroot@localhost: performance_schema 10:44:00 > help relaylogs # try the second Nothing foundPlease try to run' help contents' for a list of all accessible topicsroot@localhost: performance_schema 10:44:06 > help relaylogs # try the third Nothing foundPlease try to run 'help contents' for a list of all accessible topicsroot@localhost: performance_schema 10 : 44:09 > help relaylog # try the fourth time Oy, succeeded Name: 'SHOW RELAYLOG EVENTS'Description:Syntax:SHOW RELAYLOG EVENTS [IN' log_name'] [FROM pos] [LIMIT [offset,] row_count] # that's how Shows the events in the relay log of a replication slave is used. If you do notspecify 'log_name', the first relay log is displayed. This statementhas no effect on the master.URL: http://dev.mysql.com/doc/refman/5.7/en/show-relaylog-events.html

PS: this is actually equivalent to the keyword given in the help statement to match the name field of the help_keyword table. If a record is returned, use the four tables help_category, help_keyword, help_relation and help_ topic to do a complex association query, right join the name field in the help_ topic table, return help if a unique record is returned, and return a keyword list if multiple rows are returned. Use these specific keywords to find specific help information, such as:

Root@localhost: performance_schema 11:05:06 > help where.where is one of the followingtopics: # returns a list of keywords using where as the keyword, indicating that where will also be used in combination with these three keywords Detailed usage of where A random keyword from the list shows DELETEHANDLERUPDATEroot@localhost: performance_schema 11:09:05 > help deleteName: 'DELETE'Description:Syntax:DELETE is a DML statement that removes rows from a table.Single-Table SyntaxDELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_ name [part (partition_name) [WHERE where_condition] # where keyword is used here [ORDER BY.] [LIMIT row_count]. (2) what if I don't remember anything?

If you don't remember anything, you can only use the stupidest method, carpet search.

First of all, let's just type a few letters to the help sentence, for example: help xxx

Root@localhost: performance_schema 10:09:49 > help xxx;Nothing found # this sentence tells you that the help information was not found # never mind, the following sentence tells you to use the help contents statement to list all the possible help topic information Please try to run 'help contents' for a list of all accessible topics

Then, view all the topic categories

Root@localhost: performance_schema 10:31:47 > help contentsYou asked for help about help category: "Contents" For more information, type 'help', where is one of the followingcategories:Account ManagementAdministration # by topic or topic category name, roughly determine that the statement to view the content of relaylog events should belong to the management statement Compound StatementsData DefinitionData ManipulationData TypesFunctionsFunctions and Modifiers for Use with GROUP BYGeographic FeaturesHelp MetadataLanguage StructurePluginsProceduresStorage EnginesTable MaintenanceTransactionsUser-Defined FunctionsUtility

Use help Administration to view all keywords under this help topic

Root@localhost: performance_schema 10:37:27 > help Administration.SHOW PROCEDURE CODESHOW PROCEDURE STATUSSHOW PROCESSLISTSHOW PROFILESHOW PROFILESSHOW RELAYLOG EVENTS # found it, here it is.

Use the SHOW RELAYLOG EVENTS statement to view specific help information

Root@localhost: performance_schema 10:41:53 > help SHOW RELAYLOG EVENTSName: 'SHOW RELAYLOG EVENTS'Description:Syntax:SHOW RELAYLOG EVENTS [IN' log_name'] [FROM pos] [LIMIT [offset,] row_count] # that's how you use Shows the events in the relay log of a replication slave. If you do notspecify 'log_name', the first relay log is displayed. This statementhas no effect on the master.URL: http://dev.mysql.com/doc/refman/5.7/en/show-relaylog-events.html

OK, now that you have a clear understanding of the composition of the MySQL help system and what kind of help information help can provide to us, here is a little more knowledge:

The search keyword given in the HELP statement is case-insensitive

The search keywords can contain the wildcard characters% and _, and the effect is the same as the pattern matching operation performed by the LIKE operator. For example: HELP 'rep%' returns a list of topics that begin with rep

If the help category string or help topic string contains multiple characters, you can use quotation marks or no quotation marks. To avoid ambiguity, it is best to use quotation marks.

Matters needing attention related to help information table

For database instances that participate in replication, there are some considerations for helping table updates. The help table will be written to binlog by default (because these help tables match the version, upgrade the version of one instance, and other instances also need to be updated synchronously), so you need to consider whether you need to update these updates synchronously to the slave library through the master library binlog when upgrading the master library help table.

If the version of the master-slave library is different, then the master-slave library needs to upgrade the help table separately.

If it is a version of MySQL prior to 5.7.5, the master and slave libraries upgrade the help table separately using the command: mysql-- init-command= "SET sql_log_bin=0" mysql < fill_help_tables.sql

If it is MySQL 5.7.5 or later, you do not need to use-- init-command= "SET sql_log_bin=0", because the fill_help_tables.sql file contains SET sql_log_bin=0, so the master and slave libraries only need to execute the command: mysql mysql < fill_help_tables.sql

If the master-slave version is the same, then the master-slave library can update the help table of the slave library by upgrading in the master library and copying it.

For MySQL versions prior to 5.7.5, you only need to execute the command in the main library: mysql mysql < fill_help_tables.sql

If it is MySQL 5.7.5 or later, you need to modify the ll_help_tables.sql file in the main library server, remove the SET sql_log_bin=0, and then execute the command in the main library: mysql mysql < fill_help_tables.sql

The above is all the contents of the article "how to use help commands 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.