Create and delete indexes in the MySQL database

The following articles mainly describe the creation and deletion of MySQL databases, as well as the detailed parsing of indexes and changing actual table statements. If you are also popular with MySQL databases, you can click the following article to have a better understanding of it. The following is the specific content of the article. 1. CREATETABLE and DROPTA can be used to create, delete, index, and modify tables.

The following articles mainly describe the creation and deletion of MySQL databases, as well as the detailed parsing of indexes and changing actual table statements. If you are also popular with MySQL databases, you can click the following article to have a better understanding of it. The following is the specific content of the article. 1. CREATE, delete, index, and modify tables using create table and DROP TA

The following articles mainly describe the creation and deletion of MySQL databases, as well as the detailed parsing of indexes and changing actual table statements. If you are also popular with MySQL databases, you can click the following article to have a better understanding of it. The following is the specific content of the article.

1. Create, delete, index, and modify a table

You can use the create table, drop table, and alter table statements to CREATE and Delete tables and change their structure. Each of these statements has a dedicated MySQL database extension, which makes each statement more useful. The create index and drop index statements allow you to add or delete existing tables.

.

Ii. create table statement

Use the create table statement to CREATE a TABLE. The complete Syntax of this statement is quite complex, because there are so many optional clauses, but in reality the application of this statement is quite simple. For example, all the create table statements we use in Chapter 1st are not that complex. Interestingly, most of the complicated things are clauses that MySQL databases will discard after analysis. See Appendix D to see these complex things.

CHECK the terms of the create table statement. note how many syntaxes are used in the references constraint and CHECK clauses. These clauses involve external keys, integrity of references, and input value constraints. MySQL does not support these functions, but it analyzes its syntax to make it easier to use the table definitions created in other database systems. (This code can be used more easily with less editing work .)

If you write your own table description from the beginning, you can ignore these clauses. We will not introduce them much in this section. Create table should at least indicate the TABLE name and the list of columns in the TABLE. For example:

In addition to the columns that make up the table, you can also explain how to index the table when creating the table. Another option is to create a table without indexing, and then add an index later. If you plan to fill the table with a large amount of data before you start using the table for query, it is a good way to create an index later. When each row is inserted, the index is updated to a non-indexed table

Creating an index is much slower. We have introduced the basic syntax of the create table statement in Chapter 1st, and discussed how to describe the column type in Chapter 2nd. It is assumed that you have read these two chapters, so we will not repeat them. In this section, we will introduce some important extensions to the create table statement in MySQL3.23. These extensions provide great flexibility in TABLE construction. These extensions are:

Table Storage type description.

Create a table only when the table does not exist.

The temporary table is automatically deleted when the client session ends.

Create a table by selecting the data you want to store in the table.

1. Table Storage type description

Before MySQL3.23, all tables created by users use the ISAM storage method. In MySQL3.23, TYPE = type can be specified after the list of columns in the create table statement, and tables can be explicitly created in three types. The type can be MYISAM, ISAM, or HEAP. For example:

It may not be a good idea to convert a table to the HEAP type. However, if you want the table to be maintained until the server is disabled, you can perform this conversion. The HEAP table remains in the memory until the server exits. The three table types have the following features:

MyISAM table. The MyISAM storage format is the default type in MySQL databases since MySQL 3.23. It has the following features:

If the operating system itself allows larger files, the files are larger than the ISAM storage method.

Data is stored in an independent machine format with low byte priority. This means that tables can be copied from one machine to another, even if their architecture is different.

Value index values occupy less storage space, because they are stored preferentially by high bytes. The index value changes rapidly in the low byte, so the high byte is easier to compare.

The AUTO_INCREMENT process is better than the ISAM table. The details are discussed in Chapter 2.

Several index restrictions are reduced. For example, you can index columns with NULL values or BLOB and TEXT columns.

To improve the table integrity check, each table has a flag. After checking the myisamchk table, set this flag. You can use myisamchk-fast to skip the check that the table has not been modified since the previous check, so that this management task is faster. The table also shows whether the table is normally disabled. If the server is shut down abnormally or the machine crashes, this flag can be used to detect the table to be checked when the server starts.

ISAM table. The ISAM storage format is the oldest format used by MySQL3.23, but it is still available. Generally, compared with ISAM tables, MyISAM tables are preferred because they have fewer restrictions. The support for ISAM tables may gradually disappear as the storage format is supported by MyISAM tables.

HEAP table. The HEAP Storage Format creates a table that uses a fixed-length row memory, which enables the table to run very fast. When the server is stopped, they will disappear. In this sense, these tables are temporary. However, compared with the temporary table created using the create temporary table, the heap table is visible to other clients. The HEAP table has several restrictions. These restrictions do not apply to MyISAM or ISAM tables, as shown below:

The index is only used for comparison between "=" and "<=>.

The index Column cannot contain NULL values.

BLOB and TEXT Columns cannot be used.

The AUTO_INCREMENT Column cannot be used.

2. Create a table that does not exist

To CREATE a non-existent TABLE, use create table if not exists. In an application, you cannot determine whether the table to be used already exists. Therefore, you must create such a table. The if not exists modifier is extremely useful for scripts that use MySQL to Run batch jobs. Here, the common create table statement works

Not very good. This is because these tables are created when the job runs for the first time. If these tables already exist, an error will occur during the second run. IF the if not exists statement is used, no problem occurs. Each time a job is run, a table is created as before. If these tables already exist, the table creation fails during the second operation, but no error occurs. This allows the job to continue running, just as the attempt to create a table has been successful.

3. Temporary table

Create temporary table can be used to create temporary tables. These tables will automatically disappear at the end of the session. It is convenient to use temporary tables because you do not have to worry about releasing the drop table statement to explicitly Delete these tables. If your session ends abnormally, these tables will not be stranded. For example, if a file contains a query that runs in the MySQL database and you decide to stop it before it ends, you can stop the query in the middle of its execution, and there is no problem, the server will delete any temporary tables created. In the old version of MySQL, there are no real temporary tables, except that you think they are temporary in your mind.

For applications that require such tables, remember to delete them. If you forget to delete the table or encounter an error in the client where it exists, these tables will exist until someone notices and deletes them. The temporary table is only visible to the client that created the table. Its name can be the same as an existing Permanent Table. This is not an error and will not cause problems with existing permanent tables.

Assume that a temporary table named member is created in the samp_db database. The original member table becomes hidden (inaccessible). References to the member will reference the temporary table. If a drop table member statement is published, the temporary TABLE will be deleted, and the original member TABLE will "reappear ". If you simply interrupt the connection to the server without deleting the temporary table, the server will automatically delete it. Next connection,

The original member table is visible again. The name hiding mechanism only works at one level. That is, you cannot create two temporary tables with the same name.

4. Create a table using the SELECT result

An important concept of relational database is that any data is expressed as a table composed of rows and columns, and the results of each SELECT statement are also a table composed of rows and columns. In many cases, a "table" from SELECT is only an image of rows and columns that scroll along with your work on the display. Before MySQL3.23, if you want to save the SELECT results in a table for future query, you must make special arrangements:

1) Run DESCRIBE or show columns query to determine the column type in the table from which you want to obtain information.

2) create a table and specify the name and type of the column you just viewed.

3) after the table is created, an INSERT... SELECT queries, retrieves results, and inserts them into the created table.

All changes have been made in MySQL3.23. Create table... The SELECT statement eliminates these time-wasting tasks, so that a new table can be obtained directly using the results of the SELECT query. The task can be completed in one step without having to know or specify the Data Type of the retrieved column. This makes it easy to create a table that is fully filled with your favorite data and prepares for the further query.

You can copy a table by selecting all the content of a table (without the WHERE clause), or using

The WHERE clause that fails to create an empty table, for example:

If you want to use load data to LOAD a DATA file into the original file, and are not sure whether the specified DATA format is correct, it is useful to create an empty copy. You do not want to end with a malformed log in the original table when you do not get the correct option for the first time.

Using the empty copy of the original table allows you to experiment with the load data option for specific columns and row delimiters until you are satisfied with the interpretation of the input DATA. After you are satisfied, you can load the data into the original table.

You can use create temporary table and SELECT to CREATE a temporary table as its own copy, for example:

This allows you to modify the content of my_tbl without affecting the original content. This is useful when you want to experiment with queries that modify table content, but do not want to change the original table content. To use the pre-written scripts with the original TABLE name, you do not need to edit these scripts to introduce different tables. You only need to add the create temporary table statement at the beginning of the script. The corresponding script creates a temporary copy and performs the copy operation. When the script ends, the server automatically deletes the copy.

To CREATE a table as its own empty copy, it can be used with create temporary... SELECT uses the WHERE 0 clause together, for example:

Note the following when creating an empty table. When you create a table that is filled by data selection, its column names come from the selected column names. If a column is calculated as the result of an expression, the column "name" is the text of the expression. The expression is not a legal column name. You can run the following query in MySQL to learn about this:

If you select columns with the same names from different tables, it may be difficult. Assume that both table t1 and table t2 have column c, and you want to create a table from all the combinations of rows in the two tables.

You can provide aliases to specify the unique column names in the new table, for example:

Create a table by selecting data for filling and the index of the original table is automatically copied.

3. drop table statement

Deleting a table is much easier than creating a table, because you do not need to specify anything about its content. You only need to specify its name, for example:

 
 

  
  
DROP TABLE tb1_name 
 
 

MySQL expands the drop table statement in some useful aspects. First, you can specify several tables in the same statement to delete them, for example:

 
 

  
  
DROP TABLE tb1_name1,tb1_name2,…… 
 
 

Second, if you are not sure whether a table exists, but want to delete it if it exists. You can add if exists in this statement. In this way, MySQL will not issue an error message if the TABLE given in the drop table statement does not exist. For example:

 
 

  
  
DROP TABLE IF EXISTS tb1_name 
 
 

If exists is useful in the script used by MySQL, because by default, MySQL will exit when an error occurs. For example, if you have an installation script that can create tables, these tables will continue to be used in other scripts. In this case, you do not need to worry about the script used to create the table when it starts running. If you use a normal drop table at the beginning of the script, it will fail during the first run because these tables have never been created. IF you use if exists, no problem will occur. When tables already exist, delete them. If they do not exist, the script continues to run.

4. Create and delete Indexes

Indexes are the main means to accelerate table content access, especially for queries involving connections to multiple tables. This is an important part of Chapter 4th "query optimization". Chapter 4th discusses why indexes are required, how indexes are used, and how they are used to optimize queries. This section describes the features of indexes and the syntax for creating and deleting indexes.

1. Index features

MySQL provides great flexibility for index construction. You can index a combination of single or multiple columns. If you want to find a value from different columns of a table, you can also construct more than one index in a table. If a column is of the string type rather than the ENUM or SET type, you can choose to index only the n characters at the leftmost of the column. If the first n characters in the column are the most unique, this will not sacrifice performance, but will also greatly improve the performance: using the index column prefix rather than the entire column can make the index smaller and faster access. Although there will be fewer and fewer constraints for the further development of MySQL databases to create indexes, there are still some constraints. The following table lists the differences between the ISAM table and MyISAM Table Based on the index features:

From this table, we can see that for the ISAM table, the index column must be defined as not null, And the BLOB and TEXT Columns cannot be indexed. The MyISAM Table type removes these restrictions and slows down other restrictions. The differences between the two types of table indexes indicate that, depending on the MySQL version used, some columns may not be indexed. For example, if you use a version earlier than version 3.23, you cannot index a column that contains a NULL value.

If MySQL3.23 or an updated version is used, but the TABLE was previously created using an isam table, you can use alter table to easily convert them to the MyISAM storage format, this allows you to use some new indexing functions, such:

Alter table tb1_name TYPE = MYISAM

2. Create an index

When executing the create table statement, you can CREATE an INDEX for the new TABLE. You can also use create index or alter table to add an INDEX for an existing TABLE. Create index is introduced in MySQL 3.23. However, if you use a version earlier than MySQL 3.23, you can use the alter table statement to CREATE an INDEX (MySQL usually maps the crecreate INDEX to alter table in the internal part ). You can specify whether the index can contain duplicate values. If not, the index should be created as a primary key or UNIQUE index. For a single column unique index, this ensures that the column does not contain duplicate values. The unique index of multiple columns ensures that the combination of values is not repeated.

The primary key index is very similar to the UNIQUE index. In fact, the primary key index is only a UNIQUE index with the name PRIMARY. This indicates that a table can only contain one primary key, because a table cannot have two indexes with the same name. The same table can have multiple UNIQUE indexes.

Large.

To add an INDEX to an existing TABLE, you can use the alter table or create index statement. Alter table is most commonly used because it can be used to create common indexes, UNIQUE indexes, or primary key indexes, such:

Tbl_name indicates the name of the table to be indexed, while column_list indicates which columns are indexed. If an Index consists of more than one column, each column name is separated by a comma. The index name index_name is optional, so you can leave it empty. MySQL will assign it a name based on the first index column. Alter table allows you to specify changes to multiple tables in a single statement. Therefore, you can create multiple indexes at the same time.

Create index can add a common INDEX or unique index to a table, for example:

Tbl _ name, index_name, and column_list have the same meaning as the alter table statement. The index name is optional. You cannot use the create index statement to CREATE a primary key index. To CREATE an index for a new TABLE when the create table statement is released, the syntax used is similar to the syntax of the alter table statement. However, you should specify the index creation clause in the statement section of the TABLE column that you define, as shown below:

Like alter table, the INDEX name is optional for INDEX and UNIQUE. If not given, MySQL database selects one for it. There is a special case: you can add a primary key after the column definition to create a single column primary key index, as shown below:

In the preceding example, not null is specified for the index column. If it is an ISAM table, this is required because you cannot index columns that may contain NULL values. For MyISAM tables, the index column can be NULL, as long as the index is not a primary key index.

If you index the prefix of a string column (n characters to the leftmost of the column value), the column_list operator is applied to indicate that the syntax of the column is col_name (n) instead of c o l _ name. For example, the first statement below creates a table with two CHAR columns and an index composed of these two columns. The second statement is similar, but only the prefix of each column is indexed:

In some cases, you may find that you must index the column prefix. For example, the length of an index row has a maximum limit. Therefore, if the length of an index column exceeds this limit, you may need to use the prefix to index the row. In the MyISAM Table index, prefix indexes are also required for BLOB or TEXT columns. The index of a column is limited for future changes to this column. You cannot delete this index and use a short prefix, shorten this column to a column with a length less than the index prefix.

3. delete an index

You can use the drop index or alter table statement to delete an INDEX. Similar to the create index statement, drop index is usually processed internally as an alter table statement, and drop index is introduced in MySQL3.22. The syntax for deleting an index is as follows:

The first two statements are equivalent. The third statement is only used to delete the primary key index. In this case, no index name is required because a table may only have one such index. If an index is not explicitly created as the primary key, but the table has one or more UNIQUE indexes, MySQL deletes the first of these UNIQUE indexes.

If a column is deleted from the table, the index may be affected. If the column to be deleted is an index component, the column is also deleted from the index. If all the columns that make up the index are deleted, the entire index is deleted.

V. alter table statement

The alter table statement is a common statement in MySQL and can be used for many tasks. We have read several of its functions (creating and deleting indexes and converting tables from one storage format to another ). This section describes some of its other features. The complete Syntax of alter table is described in Appendix D.

Alter table is useful when you find that the structure of a TABLE does not reflect what you want. You may want to use this table to record other information or contain redundant values. Or some columns are too small, or their definitions are too large to be actually needed. You need to reduce them to save storage space. Or the TABLE name given when the create table statement is released is incorrect. And so on. You can use the alter table statement to solve such problems. The following are some examples:

You are manipulating a Web-based questionnaire that uses each submitted questionnaire as a record in the table. Later, I decided to modify the questionnaire to add some questions. In this case, you must add some columns to the table to store new problems.

You are managing a research project. Use the AUTO_INCREMENT column to assign the case number to study the record. You do not want to extend the funds too long to generate more than 50 000 records. Therefore, the column type is unsigned smallint, and the maximum unique value it can store is 65 535. However, the project funding has been extended, and it seems that an additional 50 million records may be generated. In this case, the type of this column needs to be larger to store more parts.

The size change may also be in the opposite direction. A CHAR (255) column may be created, but it is found that there are no strings longer than 100 characters in the table. This column can be shortened to save storage space. The alter table syntax is as follows:

Each action indicates a modification made to the table. MySQL database expands the alter table statement, allowing multiple actions to be specified. Actions are separated by commas. This is useful for reducing keyboard input, but the more important reason for this expansion is that, unless all VARCHAR columns can be changed to CHAR columns at the same time, otherwise, it is impossible to change the table from a table with variable row length to a table with fixed row length.

The following example shows some alter table functions.

Rename the table. This is simple; you only need to give the old table name and the new table name:

If a temporary table exists in MySQL3.23, renaming a temporary table as a database name will hide the original table. If a temporary table exists, the original table will be hidden. This is similar to creating a temporary table with the same name to hide a table.

Change the column type. To CHANGE the column type, use the CHANGE or MODIFY clause. If the column in my _ t B l is SMALLINT UNSIGNED, you want to change it to the column MEDIUMINT UNSIGNED. Use any of the following commands to complete this task:

Why do I give the column name twice in the CHANGE command? One thing that MODIFY cannot do is to CHANGE the column name in addition to the type. If you want to rename I as j while changing the type, follow these steps:

It is important to name the column you want to change and describe the complete definition of a column including the column name. Even if you do not change the column name, you must include the corresponding column name in the definition.

One important reason for changing the column type is to improve the efficiency of the join query between the two tables. It is faster when the two columns are of the same type. Perform the following query:

If t1.name is CHAR (10) and t2.name is CHAR (15), the running speed of this query is not as high as that of CHAR (15. You can use any of the following commands to change t1.name to make them of the same type:

For MySQL versions earlier than, the connected columns must be of the same type. Otherwise, the indexes cannot be used for comparison. For versions 3.23 or later, the index can be used for different types, but if the types are the same, the query is still faster.

Converts a table from a variable long row to a fixed long row. Assume that the chartbl table has a VARCHAR column and you want to convert it to a CHAR column to see what performance improvements can be achieved. (Tables with fixed-length rows are generally faster than tables with variable-length rows .) This table is created as follows:

The problem here is that you need to change all columns at one time in the same alter table statement. It is impossible to complete the change in one column at a time, or this attempt will not work. If you run DESCRIBE chartbl, you will find that the two columns are still VARCHAR columns! The reason is that if one column is changed each time and MySQL notices that the table still contains a Variable Length Column, it will re-convert the changed column to VARCHAR to save space. To solve this problem, you should change all VARCHAR columns at the same time:

Now DESCRIBE will show that the table contains all CHAR columns. Indeed, this type of operation is very important because it enables alter table to support multiple actions in the same statement.

Note that when you want to convert a table like this: if the table contains BLOB or TEXT columns, the attempt to convert the table to a fixed-length row format fails. Even if there is only one variable-length column in the table, the table will have a variable-length row, because these variable-length column types do not have a fixed-length equivalence.

Converts a table from a fixed-length row to a variable-length row. Although chartbl is faster with a fixed-length line, it occupies more space and therefore decides to convert it back to the original form to save space. This type of conversion is easier. MySQL automatically converts a CHAR column to a VARCHAR column. To convert a chartbl table, you can use either of the following statements:

Type of the conversion table. If you upgrade from MySQL 3.23 or later to version or later, there may be some old tables that were originally created as ISAM tables. If you want to format them as MyISAM, perform the following operations:

Why? As described in the "Create and delete indexes" section, one reason is that the MyISAM storage format has the index features that some ISAM formats do not have, for example, you can index NULL values, BLOB, and TEXT columns. Another reason is that MyISAM tables are independent from machines, so you can copy them directly to another machine, even if those machines have different hardware architectures. This will be further described in Chapter 11th.