1. Table creation statement:
CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`), UNIQUE KEY `idxunique_first_name_gender` (`first_name`,`gender`), UNIQUE KEY `uniq_idx_firstname` (`first_name`,`gender`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2. Add unique key:
-- Add touniqueindex alter table employees add constraint idxunique_first_name_gender unique(first_name, gender);
3. Query test statement:
-- Contains all fields in the index，And the order and index are consistent select * from employees where first_name='Chirstian' and gender='M'; -- keyColumn value:idxunique_first_name_gender -- Contains all fields in the index，But the order and index are inconsistent select * from employees where gender='M' and first_name='Chirstian'; -- keyColumn value:idxunique_first_name_gender -- Includes the first field of the index select * from employees where first_name='Chirstian'; -- keyColumn value:idxunique_first_name_gender -- Non-first field containing the index select * from employees where gender='M'; -- keyColumn value:NULL
4. During query, if there are only columns (partial or all) in the where condition and the column value type is const. If you want to use a unique key, there must be the first column when creating a unique key, and the other columns are free.
A question related to mysql unique key
The user_id of xxx_tab is unique key, let’s take a look at the pseudo-code:
get user_id=10000 from xxx_tab;
if exist
{
return ok
}
ret = insert user_id=10000
if ret ok
{
return ok
}
return not_ok
Question: Why is the final result not_ok?
Tip: Distributed Impact
Summarize
The above is the entire content of this article. I hope that the content of this article has certain reference value for your study or work. Thank you for your support. If you want to know more about it, please see the following links