MySQL Strengthen knowledge points

1. INSERT INTO… ON DUPLICATE KEY usage

​ When in insert At the end of the statement is specified on duplicate key update When the sentence is , If the newly inserted new data a Column The value of already exists in the database , The following... Will be executed update sentence .

Pass one SQL To illustrate ：

INSERT INTO TableName(a,b,c) 
VALUES(1,2,3)
ON DUPLICATE KEY
UPDATE c=c+1;

explain ：

​ Suppose the fields in the above statement a The attribute of is unique unique, When in insert At the end of the statement is specified on duplicate key update When the sentence is , If the newly inserted new data a Column The value of already exists in the database , The following... Will be executed update sentence , This is equivalent to executing the following statement ：

UPDATE TableName SET c=c+1 WHERE a=1;

conversely , Execute normal insert sentence .

** Attention should be paid to ：** If the row is inserted as a new record , Then the value of the affected row is 1; If the original record is updated , Then the value of the affected row is 2.

Through another SQL Let's take a look at inserting multiple rows of records at the same time ：

Field a Is defined as UNIQUE, And the original database table table A record already exists in (2,2,9) and (3,2,1), If the record is inserted a The value is repeated with the original record , Then update the original record , Otherwise insert a new line ：

INSERT INTO TABLE (a,b,c) VALUES 
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

Execute as above , Find out (2,5,7) Medium a Compared with the original records (2,2,9) Unique value conflict occurred , execute ON DUPLICATE KEY UPDATE, Transfer the original record (2,2,9) Update to (2,5,9), take (3,2,1) Updated to (3,3,1), Insert new record (1,2,3) and (4,8,2)

2. replace into…values usage

replace into Follow insert into The function is similar to , The difference is ：replace into First try inserting data into the table ,

1. If you find that this row of data already exists in the table （ Based on the primary key or unique index ） Then delete this row of data first , Then insert the new data ;
2. otherwise , Insert new data directly .

It should be noted that ： The table that inserts data must have a primary key or a unique index ！ Otherwise ,replace into Will insert data directly , This will result in duplicate data in the table .

amount to ：

if not exists (select 1 from t where id = 1) 
insert into t(id, update_time) values(1, getdate()) 
else 
update t set update_time = getdate() where id = 1;

Use REPLACE INTO, Must have a watch INSERT and DELETE jurisdiction .

REPLACE Statement will return a number , Come to know the number of rows affected . This number is the sum of the number of rows deleted and inserted . If for a single line REPLACE The number is 1, Then a line is inserted , At the same time, no lines have been deleted . If the number is greater than 1, Before the new line is inserted , One or more old lines have been deleted . If the table contains multiple unique indexes , And the new row copies the values of different old rows in different unique indexes , It is possible that a single line replaces multiple old lines .

The number of affected rows can be easily determined whether REPLACE Only one line has been added , Or Yes No REPLACE It also replaced other lines ： Check if the number is 1 （ add to ） Or bigger （ Replace ）.

at present , You cannot be in a subquery , Change... To a table , Select... From the same table at the same time .

Here is a more detailed description of the algorithm used （ The algorithm is also used for LOAD DATA…REPLACE）：

1. Try inserting new rows into the table
2. When the insert fails due to a duplicate keyword error for the primary key or unique key ：
   • a. Delete conflicting rows with duplicate keyword values from the table
   • b. Try inserting the new row into the table again

Three forms ：

1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...

PS：

mysql There are three kinds of statements to insert data ：

• insert into Means insert data , The database checks the primary key , If there is a repetition, an error will be reported ;
• replace into Indicates insert replacement data , What is in the demand table Primary Key, perhaps unique Indexes , If the database already has data , Replace with new data , If there's no data effect, it's the same as insert into equally ;
• insert ignore Express , If the same record already exists in the table , Ignore the current new data ;

3. where 1 = 1 explain

select * from table where 1=1, among where 1=1, because 1=1 Forever is the return of establishment true, Condition is true , So this statement is equivalent to select * from table, Return all data in the query table , It will not affect the query speed and cause sql Inject .

Use where 1=1 Scene ：

String sqlStr = "select * from table where 1=1";
if(user.getUsername()!=""){
sqlStr = sqlStr + "username=" + user.getUsername();
}
if(user.getPassword()!=""){
sqlStr = sqlStr + "and password=" + user.getPassword();
}

Above sql The implication is ： add 1 = 1 Can cope with changing query conditions , It's just a way to construct a dynamic that can run correctly in order to meet the various uncertain factors in the multi condition query of mutton noodles SQL A method of statement , There is no other effect .

mybatis The frame can pass through where Label instead of 1=1 The role of .

<select id="getUserInfo" resultType="map">
		SELECT username,sex
		FROM user
		<where>
			<if test=" username!='' and username!= null ">
	         username = #{username}
			</if>
			<if test=" sex!='' and sex!= null ">
	         AND sex = #{sex}
			</if>
		</where>
</select>

where Labels meet one or more if Only insert when the condition has value ”WHERE“ Clause , And if the final content is ”AND“ perhaps ”OR“ At the beginning ,where Labels also know how to remove them .