mysql自增guid Mysql自增id冲突

mysql 忽略主键冲突、避免重复插入的几种方式

insert ignore into table_name(email,phone,user_id) values('[e-mail]','99999','9999')

,这样当有重复记

insert ignore into table(name)  select  name from table2

 

1. replace into table_name(col_name, ...) values(...) 

2. replace into table_name(col_name, ...) select ... 

3. replace into table_name set col_name=value, ...

 

replace  into table_name(email,phone,user_id) values('test569','99999','123')

 

if not exists (select phone from t where phone= '1') 

            insert into t(phone, update_time) values('1', getdate()) 

 else 

          update t set update_time = getdate() where phone= '1'

 

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)   

          ->ON DUPLICATE KEY UPDATE c=c+1; 

mysql>UPDATE table SET c=c+1 WHERE a=1;

 

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

 

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)    

        -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

 

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)   

    -> ON DUPLICATE KEY UPDATE c=3;

mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)   

         -> ON DUPLICATE KEY UPDATE c=9;

 

INSERT INTO table_name1(title,first_name,last_name,email,phone,user_id

,role_id,status,campaign_id)

SELECT '','','',table_name2.email,table_name2.phone,NULL,NULL,'pending',29

 FROM table_name2  

WHERE table_name2.status = 1 

ON DUPLICATE KEY UPDATE table_name1.status = 'pending'

 

insert into class select * from class1

ON DUPLICATE KEY UPDATE class.course = class1.course

 

insert into test(a) values(null)

                    insert into test(a) values(null)

 

是可以重复插入的（联合唯一索引也一样）。