The Use of mysql unique key in Query and Related Problems

  • 2021-11-24 03:12:08
  • OfStack

1. Table building 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 unique Index 
alter table employees add constraint idxunique_first_name_gender unique(first_name, gender);

3. Query test statements:


--  Contains all the fields of the index, and the order and index 1 To 
select * from employees where first_name='Chirstian' and gender='M';
-- key Column value :idxunique_first_name_gender
--  Contains all the fields of the index, but the order and index do not 1 To 
select * from employees where gender='M' and first_name='Chirstian';
-- key Column value :idxunique_first_name_gender
--  Include index columns 1 Fields 
select * from employees where first_name='Chirstian';
-- key Column value :idxunique_first_name_gender
--  Contains the index's non-first 1 Fields 
select * from employees where gender='M';
-- key Column value :NULL

4. When querying, if there are only columns (part or all) in unique key in where condition and the column value type is const. If you want to use unique key, where must have the first column when creating unique key, and other columns are optional.

1 question related to mysql unique key

user_id of xxx_tab is unique key, 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 impacts

Summarize


Related articles: