Friday, August 14, 2020
dbametrix
More
    Home Database Technology Help tool of mySQL

    Help tool of mySQL

    MySQL has a convenient HELP command that allows you to quickly find explanations on one aspect of the SQL language without having to resort to documentation or other manuals. Let’s see how to use it.

    Fill in the help tables of mySQL:

    Often the help contents are not installed by default, so you need to load them manually. They can be downloaded from the documentation page on the official MySQL website. What we are going to download is a compressed package containing a text file, which in turn contains the SQL queries that will populate some system tables. Once downloaded we will unpack it in a convenient location, assign it a short name (like help.sql) and launch the following command from the Terminal:

    - Advertisement -
    dbametrix

    mysql --user=myuser --password=mypassword mysql help.sql

    Of course you need to replace myuser and mypassword with a real existing username and password. If they are correct, MySQL should quickly enter the help contents into the system tables.

    The syntax of the HELP command is as follows:

    HELP _topic_.

    - Advertisement -
    dbametrix

    You can also use it from the command line:

    help _argument_

    In both cases the argument is a string (so in SQL you have to enclose it between quotes!) and the use of upper or lower case characters is indifferent.

    Topic can be any aspect of the SQL language on which we need information. For example, if we want information about HELP, we write in SQL:

    HELP ‘help

    If the search returns more topics:

    We often get what we’re looking for right away. In some cases, however, as in the case of HELP, the search will produce two or more results.

    If this happens, the command will return a recordset of two fields:

    • name: is the name of the argument found;
    • is_en_category: is ‘Y’ if it is an argument category, ‘N’ if it is a single argument;
    • source_category_name: this field is not always present if it indicates the category in which the argument (or subcategory) in question is located.

    In this case we will have ‘HELP COMMAND’ and ‘HELP STATEMENT’, which are arguments and not categories. We can view them with:

    HELP 'help command
    or:
    HELP 'help statement

    The first refers to help as a command to be entered in the command line, the second to HELP as an SQL statement.

    Navigate between categories:

    If we look for the name of a category, we can explore its content. We will then see the topics it contains (if any), the father category (if any) and the daughter categories (if any).

    HELP 'functions

    Since among the results we will have ‘String Functions’, we can decide to explore its content:

    HELP 'String Functions'.

    The root category, which contains all the others, is ‘Contents’.

    Display a topic

    Let’s try it:

    HELP 'ascii
    HELP 'mediumint
    HELP 'like'

    We’re gonna get a three-field record:

    • name: the name of the topic;
    • description: the syntax and other explanations;
    • example: an example, if available.

    The system tables:

    There are four system tables used by HELP and they are contained in the system database ‘mysql’. Their structure is quite simple, let’s examine it.

    The categories are contained in the help_category table, which has the following fields:

    help_category_id: primary key;

    name: category name, on this field there is a UNIQUE index;

    parent_category_id: is the id of the parent category, 0 if there is no parent category;

    url: this field is always empty, it should probably contain the url corresponding to the category in the official documentation on the MySQL site.

    The arguments are contained in the help_topic table, which has the following fields:

    • help_topic_id: primary key;
    • name: name of the argument, on this field there is a UNIQUE index;
    • help_category_id: the id of the category to which the argument belongs;
    • description: the explanation of the argument;
    • example: an example;
    • url: the url of the official documentation page that corresponds to the argument.

    Since the HELP instruction never returns the url field, if you need more information, you can use the following query to quickly find out which url to consult:

    SELECT url FROM help_topic WHERE name='argument

    The last two tables probably do not interest us, but we analyse them anyway for completeness.

    Some keywords are associated with topics (not categories). They are used internally for research, in a completely transparent way, and are contained in the help_keyword table, which has the following fields:

    • help_keyword_id: primary key;
    • name: the keyword, on this field there is a UNIQUE index.

    The relationship between keywords and arguments, being a many to many relationship, is represented in the help_relation table, which has the following fields:

    • help_keyword_id: the id of a keyword;
      help_topic_id: the id of an argument.

    Both fields together make up the primary key.

    You can learn about table types of mysql and mysql virtualization articles available in this category. Dbametrix provides only Oracle DBA support on clouds and on-prem. You can find out more articles like Oracle and IBM DB2 database Comparison and Differences and Oracle and MySQL Comparison

     

    0
    - Advertisement -
    dbametrix
    Previous articleMySQL Table Types
    Next articleBig Data Analysis Tools
    - Advertisment -
    dbametrix

    Most Popular

    ORA-01940: Cannot drop a user that is currently connected

    Detail explanation and solution of ORA-01940: Cannot drop a user that is currently connected error.

    Ubuntu and Oracle SQL Developer

    Blog post explains how to install and use of Oracle SQL Developer on Ubuntu linux server

    Create Schema in Oracle 18c XE

    Article explain how to create new user in Oracle 18c XE and grant some system privileges with connect as new schema user in database.

    Oracle Autonomous JSON database

    The blog article explains about What is the Oracle autonomous JSON database, usage and how to get it from Oracle Cloud.

    Recent Comments

    Margareta Combes on nosql Security Vulnerabilities
    Gordon Coleclough on Oracle Tuning Basics Part-1
    Gordon Coleclough on ASM Disk creation issue