one, background. 1, the online version of the development, you will often encounter some of the original table field to split, the table to copy the fields to another table. This article mainly introduces several ways of updating database fields.
Two, update the way. 1, using Java code to update, but often need to read the database, update the value of the data. Suitable for handling very complex logic, but the update time is long, it may affect the online service. 2, the use of MySQL statements to update, in fact, basically can solve the problem of database updates. The processing speed is also much faster.
third, the MySQL database update mode. 1, data updates between large tables. For data that gets two fields from a millions of table, and then matches the corresponding content, save it to another table. Directly from this table to get two field data and update will be longer, and will create a lock table, in the server can not stop the update, this will be more trouble, so the better way. is a list of the required several fields are select out, and into another table, and to establish the appropriate index. Match the fields of the table to be updated so that it does not affect the functioning of the line or can be achieved quickly.
The principle is to take out the data you want, not all the data, and the corresponding data to build a good index, speed up the query speed.
Four, duplication of data search and deletion. 1, when you look for duplicate data, and the duplicate record is a field. The most used is the select * from user where is_deleted = 0 GROUP BY phone has count (*) > 1 this way. 2, but if you are looking for a duplicate record (multiple fields), you need to do it in a different way.
Select * from User a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from user group by PEOPLEID,SEQ has count (*) > 1)
* 3, delete duplicate records in duplicate records. And only the smallest ID is left.
Delete from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ has Cou NT (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)
