2. Primary key and unique key
1) The starting point/function is different: the former is the unique identifier of a row of data, while the latter is only used to avoid data duplication.
2) One or more columns of the former must all be not null; if one of the columns is null, it will become not null when it is added as the primary key. If the primary key is deleted, the nullable nature of the column will change back. . The latter column can be null.
3) A table can only have one primary key and can have multiple unique keys. [Can a table have no primary key? ? ? 】
4) For the column corresponding to the unique key, null can be inserted multiple times (although it is also a kind of repetition); this is determined by the principle of the index, that is, the index's processing of null.
create table t(id int, key (id)); If there are other keys using id (such as foreign key), use other keys to name it; If none are named, the id is used; if multiple columns are specified as keys at one time, the first column name is used as the key name.create table t(id int, key kismet(id));Specify the name of the keyconstraint: cannot be used, after all, it is ordinary The key has no binding effect
create table t(id int, primary key (id));create table t(id int, primary key kismet(id)); can be executed, but The name does not work##create table t(id int, constraint kismet primary key(id)); can be executed, but the name does not work
create table t(id int, unique key (id)); The naming rules are different from key. Only the first column is used as the key name##create table t(id int, unique key kismet( id));Specify the name of the key##create table t(id int, constraint kismet unique key(id));Specify the name of the key
create table t(id int, foreign key (dage_id) references dage(id)); can be executed, and the execution result is that a An automatically named foreign key and an automatically named ordinary key.create table t(id int, foreign key kismet(dage_id) references dage(id)); can be executed, and the execution result is that an automatically named foreign key and a normal key named kismet.create table t(id int, constraint kismet foreign key(dage_id) references dage(id)); can be executed, and the execution result is to create a table named The foreign key of kismet and a normal key named kismet.
(1) Create a table, set foreign keys, and insert dataCASCADE (Follow foreign key changes)SET NULL (Set null value)SET DEFAULTNO ACTION4. Example
(<span class="pln"><span class="str"><span class="pln"><span class="pun"></span></span></span></span>
int<span class="pln">(<span class="str">11<span class="pln">)<span class="kwd"> NOT NULL auto_increment<span class="pun">,<span class="lit"><span class="pun"><span class="pln"><span class="pun"></span></span></span></span></span></span></span></span></span>
##`name`32<span class="pln">)<span class="str"> <span class="pln">default<span class="pun"> <span class="lit">''<span class="pun">,<span class="pln"><span class="kwd"><span class="pln"><span class="str"><span class="pun"></span></span></span></span></span></span></span></span></span></span>## PRIMARY KEY </span>(
`id`<span class="pln"><span class="pun"><span class="str"><span class="pun"></span></span></span>);</span>
<span class="pun">CREATE TABLE </span>`xiaodi`
<span class="pln"><span class="str"><span class="pln"><span class="pun"></span></span></span> </span>`id`
11<span class="pln">)<span class="str"> NOT NULL auto_increment<span class="pln">,<span class="kwd"><span class="pun"><span class="lit"><span class="pun"><span class="pln"><span class="pun"></span></span></span></span></span></span></span></span># `dage_id`</span>
int)<span class="pln"> <span class="str">default<span class="pln"> NULL<span class="kwd">,<span class="pun"><span class="lit"><span class="pun"><span class="pln"><span class="kwd"><span class="pln"><span class="pun"></span></span></span></span></span></span></span></span></span>##`name`</span> varchar</span>(
32default<span class="pln"> <span class="str">''<span class="pln">,<span class="pun"><span class="lit"><span class="pun"><span class="pln"><span class="kwd"><span class="pln"><span class="str"><span class="pun"> </span></span></span></span></span></span></span>## PRIMARY KEY </span>(</span>`id`</span>),</span>
<span class="pln"><span class="pun"><span class="str"><span class="pun"> KEY </span>`dage_id`</span> </span>(</span>`dage_id`
),<span class="pln"><span class="str"><span class="pln"><span class="pun"><span class="str"><div class="L1"><code class="language-sql"><span class="pln"> CONSTRAINT <span class="str">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="pln"> REFERENCES <span class="str">`dage`<span class="pln"> <span class="pun">(<span class="str">`id`<span class="pun">)</span></span></span></span></span></span></span></span></span></span></span></span>
##);<span class="pun"></span>
insert <span class="pln">into<span class="kwd"> dage<span class="pln">(<span class="pun">name <span class="pln">)<span class="pun"> values<span class="pln">(<span class="pun">'Causeway Bay'<span class="str">);<span class="pun"></span></span></span></span></span></span></span></span></span></span>
into<span class="pln"> xiaodi<span class="kwd">(<span class="pln">dage_id<span class="pun">,<span class="pln">name<span class="pun">)<span class="pln"> values<span class="pun">(<span class="pln">1<span class="pun">,<span class="lit">'Causeway Bay_Little Brother A'<span class="pun">);<span class="str"><span class="pun"></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
]<span class="pun"> <span class="pln">delete<span class="pun"> <span class="pln">from<span class="kwd"> dage <span class="pln">where<span class="kwd"> id<span class="pln">=<span class="kwd">1<span class="pln">;<span class="pun"><span class="lit"><span class="pun"></span></span></span></span></span></span></span></span></span></span></span></span></span>
#[ <span class="pun">1451<span class="typ"> <span class="pun">-<span class="pln"> <span class="lit">Cannot<span class="pln"> <span class="pun">delete<span class="pln"> <span class="typ">or<span class="pln"> update a parent row<span class="kwd">:<span class="pln"> a foreign key constraint fails <span class="kwd">(<span class="pln">`sample`<span class="pun">.<span class="pln">`xiaodi`<span class="pun">,<span class="str"> CONSTRAINT <span class="pun">`xiaodi_ibfk_1`<span class="str"> FOREIGN KEY <span class="pun">(<span class="pln">`dage_id`<span class="str">)<span class="pln"> REFERENCES <span class="pun">`dage `<span class="str"> <span class="pun">(<span class="pln">`id`<span class="str">))<span class="pln"><span class="pun"><span class="str"><span class="pun"></span></span></span></span></span></span> </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span> </span></span></span></span></span></span></span></span></span></span>## (3) If you want to forcibly insert the younger brother without establishing the older brother, The results are as follows</span>
(<span class="pun">dage_id<span class="pln">,<span class="pun">name<span class="pln">)<span class="kwd"> values<span class="pln">(<span class="pun">2<span class="pln">,<span class="pun">'Mong Kok_Little Brother A'<span class="pln">);<span class="pun"> <span class="pln"><span class="pun"><span class="lit"><span class="pun"><span class="str"><span class="pun"></span></span></span></span></span></span></span></span></span></span> </span></span></span></span>#[</span>Err</span>]</span>
1452 <span class="pun">Cannot<span class="typ"> add <span class="pun"> or<span class="pln"> update a child row<span class="lit">:<span class="pln"> a foreign key constraint fails <span class="pun">(<span class="pln">`sample`<span class="typ">.<span class="pln">`xiaodi`<span class="kwd">,<span class="pln"> CONSTRAINT <span class="pun">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="str"> REFERENCES <span class="pun">`dage`<span class="pln"> <span class="str">(<span class="pln">`id`<span class="pun">) )<span class="str"><span class="pun"><span class="pln"><span class="str"><span class="pln"><span class="pun"><span class="str"><span class="pun"></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>##(4) Modify event trigger settings</span></span></span></span></span>
<span class="pln">alter table xiaodi drop foreign key xiaodi_ibfk_1<span class="pun">;</span></span>
##alter table xiaodi add foreign key<span class="pln"> (<span class="pun">dage_id<span class="pln">)<span class="pun"> references dage<span class="pln">(<span class="pun">id<span class="pln">)<span class="pun"> on <span class="pln">delete<span class="kwd"> cascade on update cascade<span class="pln">;<span class="pun"></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pln">(<span class="typ"><span class="pln"><span class="pun"></span></span></span></span>
50<span class="pln">)<span class="pun"> <span class="lit">not<span class="pun"> <span class="pln">null<span class="kwd">,<span class="pln"><span class="kwd"><span class="pun"></span></span></span></span></span></span></span></span></span>
# first_name varchar)<span class="pln"> <span class="pun">not<span class="lit"> <span class="pun">null<span class="pln">,<span class="kwd"><span class="pln"><span class="kwd"><span class="pun"></span></span></span></span></span></span></span></span></span>## dob date
not,<span class="pln"><span class="kwd"><span class="pln"><span class="kwd"><span class="pun"></span></span></span></span>## gender </span>enum
( <span class="pln">'f'<span class="kwd">)<span class="pun"> <span class="str">not<span class="pun"> <span class="pln">null<span class="str">,<span class="pun"><span class="pln"><span class="kwd"><span class="pln"><span class="kwd"><span class="pun"></span></span></span></span></span></span></span></span># #<div class="L0"><code class="language-sql"><span class="pln"> key<span class="pun">(<span class="pln">last_name<span class="pun">,<span class="pln"> first_name<span class="pun">,<span class="pln"> dob<span class="pun">)</span></span></span></span> </span></span></span></span>
##);<span class="pun"></span>
##1) Match all Values: Specify specific values for all columns in the index.#2) Match the leftmost prefix: You can use the index to find the person whose last name is Allen, just use the first column in the index.#3) Match column prefix: For example, you can use the index to find people whose last name starts with J. This only uses column 1 in the index.#4) Range query of matching values: You can use the index to find people whose last name is between Allen and Barrymore, using only the first column in the index.#5) The matching part is accurate and the other parts are range matched: you can use the index to find people whose last name is Allen and whose first name starts with the letter K.#6) Query only the index: If the queried columns are all located in the index, there is no need to read the value of the tuple.7) If the index field is A+B, will the A index be used when querying A+C -> Yes, it can be confirmed by using explain(4) Limitations
1) The query must start from the leftmost column of the index.#2) You cannot skip an index column. For example, you cannot use an index to find a person whose last name was Smith and who was born on a certain day.##3) The storage engine cannot use the columns to the right of the range condition in the index. For example, if your query statement is WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23', the query will only use the first two columns in the index because LIKE is a range query .
1) Hash index calculates the Hash value through the hash function for retrieval, and can find the row pointer of the data to be searched, thereby locating the data.#2) The Hash value does not depend on the data type of the column. The index of a TINYINT column is as large as the index of a long string column.3) The Memory storage engine supports non-unique hash indexes. If multiple values have the same hash code, the index saves their row pointers in a linked list to the same location. in a hash table entry.(2) Limitations
(3) Example1) Since the index only contains hash code and record pointer, MySQL cannot Avoid reading records by using indexes. But accessing records in memory is very fast and will not have much impact on performance.#2) Hash index sorting cannot be used.3) Hash index does not support partial matching of keys, because the hash value is calculated through the entire index value.4) Hash index only supports equality comparison, such as using =, IN() and <=>. For WHERE price>100, it does not speed up the query.
<span class="pln"><span class="pun"></span></span>
## fname VARCHAR) NOT NULL, <span class="pln"><span class="pun"></span></span>## lname VARCHAR
( NOT NULL, <span class="pln"><span class="pun"></span></span>
##KEY USING HASH(fname)<span class="pln"><span class="pun"></span></span>##) ENGINE=
MEMORY<span class="pln"><span class="pun"></span></span>#5. Other indexes
The above is the detailed content of Detailed introduction to key and index in MySQL. For more information, please follow other related articles on the PHP Chinese website!