MySQL Cookbook 11th Chapter Reading notes

1, create a sequence private generate sequence value

Using Auto_increment

If the ID is explicitly set to a non-null value, there are two results:

A, this value appears in the table, because the ID column is the primary key, and therefore does not allow duplication, so there will be an error

b, this value does not appear for example, your table has an ID sequence value of 1 to 8, when you insert a new navigation and set to 20, then the next auto-generated sequence starts at 21, and the value of 9-19 becomes unavailable.

2, the effect of row deletion from sequence generation

For example, in the insect table:

such as requiring only insects, removing millipede,millbug, extra ants.

Deleting 2 will bring a notch in the middle of the sequence, and MySQL will not replenish the holes in the sequence, and deleting 7 and 8 will delete the value at the top of the sequence, depending on the storage engine used:

A, for the BDB table, start with the maximum value of +1 for the current table, for example, after you delete 7 and 8, the next line will be assigned a sequence value of 7

b, for MyISAM or InnoDB tables, the sequence values are not reused, even after the deletion, starting from 9.

Change the storage engine ALTER TABLE TBL_NAME engine =myisam

3, querying sequence values

After inserting a new voyage that contains sequence values, you want to know what the sequence value is.

LAST_INSERT_ID () gets auto_increment, some max () function queries, this method is unreliable, because there is no consideration for MYSLQ's multithreaded nature, the workaround is to merge the insert and select into a single transaction, But MySQL provides last_insert_id () to be able to get the correct ID value more succinctly. After the tower returns to this linked server, the newly created auto_increment is not affected by other client operations.

LAST_INSERT_ID () only returns each client connection based on the server, avoiding interference between different users.

interface functions written using the Python MySQL API:

4, re-counting an existing sequence

When you insert a row that contains a auto_increment column, and you do not delete it, it is a sequential sequence, but if you delete it, it creates an empty

When you decide to re-serialize, for example, the ID column in the insect table is re-serialized:

Delete First:

Insert the ID entry again and configure the related properties:

The first keyword indicates that the column is set to the top column in the table, and adding auto_increment,mysql automatically serializes all rows consecutively.

Serialized successfully again!!!

When you simply delete some rows at the top of the sequence, you do not need a full serialization, (BDB is automatically processed) for MyISAM and InnoDB only need

With Auto_increment=n, you can specify the starting value of the sequence.

This allows MySQL to reset the sequence calculator to the minimum value that can be exploited.

5, serializing a table that is not serialized

For example, there are tables:

Add a sequence ID:

ALTER TABLE Tadd ID int NOT NULL AUTO_INCREMENT,ADD PRIMARY key (ID)

You can serialize an unordered table.

6, create multiple sequences using the Auto_increment bar

Link the auto_increment column with the other columns so that they are part of the same index.

You can use primary key to set multiple primary keys: For example, you need to confirm a courier, and the Mail Courier may contain types (such as Shun Fung, Yun da), mail sending people, although may be the same type of mail, send people may be the same, but when considering both, this time you need to create a multi-column sequence. Here are some examples from the book:

7, associating a table with a auto_increment value

You take the sequence value of one table as the key of another, so that you can relate the rows in the two tables.

Suppose there is a invoice table that records the goods List for the customer's order, and a inv_item table that records each of the goods included in each customer order.

A typical use is to first insert a row in the primary table and then use LAST_INSERT_ID () to get the ID of the primary table