Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Misoperations of mysql occasionally cause repeated data, such as user information table and user_info. During front-end login, multiple records are queried in the background, causing login failures

  • Solution: Check the key field mobile
    • Select mobile From user_info Group By mobile Having Count(1)>1;
GROUP BY < column name sequence > HAVING < GROUP condition expression > Count (*) = count(1); count(*) = count(1); count(*) = count(1);Copy the code

  • And check for any duplicates

    • select *from user_info where mobile in (Select mobile From user_info Group By mobile Having Count(1)>1);

  • Then change select to delete, delete the duplicate data, if successful delete all duplicate data,

    • [Err] 1093 – You can’t specify target table ‘dept’ for update in FROM clause
A deadlock occurs when a table is updated and a table is queried, and a table is queried and a table is updated. Mysql does not support updating the same tableCopy the code
  • Solution: Query the columns to be updated as a third-party table and filter the updates.
    • select *from user_info where mobile in (Select t.mobile From ( select mobile from user_info Group By mobile Having Count(1)>1 ) t );
SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL select *from user_info where mobile in (Select mobile From user_info Group By mobile Having Count(1)>1) and user_id not in (Select min(user_id) From user_info Group By mobile Having Count(1)>1);Copy the code
  • The problem is that the query above is inefficient
Search for the smallest user_id based on the repeated field mobile. And then I'm going to look at mobile and I'm not going to look at that. All duplicates (except the row with the smallest user_id) are found: select *from user_info where user_id not in (select dt.minno from (select min(user_id) as minno from user_info group by mobile HAVING COUNT(1)>1 ) dt );Copy the code
  • The above SQL is faulty, please execute with caution! Find the smallest ID, not in, then the data to be deleted cannot be the smallest ID data, will delete a lot of data, so, is to delete the maximum or minimum ID queried, in can be; The current mysql version does not support the in operation