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

This article mainly introduces the basic knowledge of mysql that is often used in our work. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone's reference. The following is to understand the basic knowledge of mysql that is often used in the work with the author.

A table with an ID self-increasing primary key. When insert has 17 records, delete 15, 16 and 17 records, restart Mysql, and then insert a record. Is the ID of this record 18 or 15?

(1) if the type of the table is MyISAM, it is 18

Because the MyISAM table records the maximum ID of the self-increasing primary key in the data file, restarting MySQL the maximum ID of the self-increasing primary key will not be lost.

(2) if the type of the table is InnoDB, it is 15

The InnoDB table only records the maximum ID of the self-increasing primary key in memory, so restarting the database or OPTIMIZE the table will result in the maximum ID loss.

(3) both engines start at 18 if they are not restarted.

(4) if you use truncate, both engines start from 1.

two。 String data types enum and set

(1) enum

Select the string data type, which is suitable for storing "radio values" in the form interface.

When setting enum, you need to give "fixed options"; when storing, only one of these values is stored.

Format the enum:

Enum ("option 1", "option 2", "option 3",...)

In fact, enum's options correspond to a number, followed by 1, 2, 3, 4, 5. There are up to 65535 options.

When using, you can use the string format of the option, or you can use the corresponding number.

(2) set

Multi-select string data types, suitable for storing "multi-selection values" of the form interface.

When setting set, you also need to give "fixed options"; when you store it, you can store several of its values.

Format the set:

Set ("option 1", "option 2", "option 3",...)

Similarly, each option value of set corresponds to a number, which in turn is 1, 2, 4, 8, 16. There are up to 64 options.

When using, you can use the string itself of the set option (multiple options are separated by commas), or you can use the sum of the numbers of multiple options (for example, 1: 2, 4, 7)

3.mysql log

Error log: record error messages, as well as warnings or correct information.

Query log: records all requests to the database, regardless of whether they are executed correctly or not.

Slow log: set a threshold to log all SQL statements that run longer than this value in the slow log file.

Binary log: records all actions that make changes to the database.

Redo log: records the submitted and modified data, which is used for a sudden power outage in the database. After restart, you can recover the data before the power outage through redolog.

Undo log: records the data before transaction modification, which is used for transaction fallback. You can recover the data before transaction through undolog.

4. The difference between myisam and innodb in MySQL

1 > .InnoDB supports things, but MyISAM does not support things

2 >. InnoDB supports row-level locks, while MyISAM supports table-level locks.

3 > .InnoDB supports MVCC, but MyISAM does not

4 > .InnoDB supports foreign keys, but MyISAM does not

5 > .InnoDB does not support full-text indexing, but MyISAM does.

(2), four major features of innodb engine

Insert buffer (insert buffer), second write (double write), adaptive hash index (ahi), pre-read (read ahead)

(3), selectcount (*) which is faster and why?

Myisam is faster because myisam maintains a counter internally that can be fetched directly.

5. The understanding of many-to-many relationship.

For example, there are two tables An and B, many-to-many means that a piece of data in table A may correspond to multiple pieces of data in table B, and some pieces of data may correspond to a piece of data in table B. (it does not mean that multiple pieces of data in Table A correspond to multiple pieces of data in Table B)

So here we need an intermediate table C, which has at least two columns, each with foreign keys created and references An and B, respectively. These two in table C are listed as joint primary keys to prevent duplication.

6.char (n) and varchar (n) where n is n characters, not bytes (above mysql5)

7.int (n) n is the number of bits displayed, not the length. No matter what n is, the length of int can only be 4 bytes.

After reading the above about the basic knowledge of mysql often used at work, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.