if not exists (select 1 from tb where username=username) begin insert into tb values(usrename,password,email) end SELECT last_insert_id()
直接用REPLACE INTO不行?存在则用新数据替换,否则插入 replace into tt(f1,f2,f3) values(....)
如果一定要用SP: DELIMITER $$ DROP PROCEDURE IF EXISTS RR2$$ CREATE PROCEDURE `test`.`RR2`(IN EE1 VARCHAR(20),IN EE2 VARCHAR(20),IN EE3 VARCHAR(20)) BEGIN SELECT COUNT(*) INTO @EE FROM USERS WHERE username=EE1; IF @EE>=1 THEN INSERT INTO USERS(username,password,email) VALUES(EE1,EE2,EE3); SELECT last_insert_id(); END IF; END$$DELIMITER ;
delimiter // create PROCEDURE insertuser( name char(20), pwd char(20), eml char(40), OUT out_autoid int) begin if not exists (select * from users where username=name) then insert into users(username, password, email) values (name, pwd, eml); set out_autoid=LAST_INSERT_ID(); else set out_autoid=0; end if; end // delimiter ;
我对mySql是新手啊,如果觉得问题太简单,也拜托给个答案吧。谢谢啦
begin
insert into tb values(usrename,password,email)
end
SELECT last_insert_id()
replace into tt(f1,f2,f3) values(....)
DELIMITER $$
DROP PROCEDURE IF EXISTS RR2$$
CREATE PROCEDURE `test`.`RR2`(IN EE1 VARCHAR(20),IN EE2 VARCHAR(20),IN EE3
VARCHAR(20))
BEGIN
SELECT COUNT(*) INTO @EE FROM USERS WHERE username=EE1;
IF @EE>=1 THEN
INSERT INTO USERS(username,password,email) VALUES(EE1,EE2,EE3);
SELECT last_insert_id();
END IF;
END$$DELIMITER ;
create PROCEDURE insertuser(
name char(20),
pwd char(20),
eml char(40),
OUT out_autoid int)
begin
if not exists (select * from users where username=name) then
insert into users(username, password, email) values (name, pwd, eml);
set out_autoid=LAST_INSERT_ID();
else
set out_autoid=0;
end if;
end
//
delimiter ;
-> uid int not null auto_increment primary key,
-> username varchar(30),
-> password varchar(30),
-> email varchar(30)
-> );
Query OK, 0 rows affected (0.13 sec)mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE adduser(uname varchar(30),pswd varchar(30),sMail varchar
(30),OUT id INT)
-> BEGIN
-> declare fExists int default -1;
-> select uid into fExists from users where username=uname;
-> if fExists>0 then
-> set id = fExists;
-> else
-> insert into users(username,password,email) values (uname,pswd,sM
ail);
-> select LAST_INSERT_ID() into id;
-> end if;
-> END;
-> //
Query OK, 0 rows affected (0.16 sec)mysql> delimiter ;
mysql>
mysql> CALL adduser('aaa','123','[email protected]',@id);
Query OK, 0 rows affected, 1 warning (0.08 sec)mysql> select @id;
+------+
| @id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql> select * from users;
+-----+----------+----------+---------+
| uid | username | password | email |
+-----+----------+----------+---------+
| 1 | aaa | 123 | [email protected] |
+-----+----------+----------+---------+
1 row in set (0.00 sec)mysql>
mysql> CALL adduser('bbb','345','[email protected]',@id);
Query OK, 0 rows affected, 1 warning (0.06 sec)mysql> select @id;
+------+
| @id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)mysql> select * from users;
+-----+----------+----------+---------+
| uid | username | password | email |
+-----+----------+----------+---------+
| 1 | aaa | 123 | [email protected] |
| 2 | bbb | 345 | [email protected] |
+-----+----------+----------+---------+
2 rows in set (0.00 sec)mysql>
mysql> CALL adduser('aaa','123','[email protected]',@id);
Query OK, 0 rows affected (0.00 sec)mysql> select @id;
+------+
| @id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql> select * from users;
+-----+----------+----------+---------+
| uid | username | password | email |
+-----+----------+----------+---------+
| 1 | aaa | 123 | [email protected] |
| 2 | bbb | 345 | [email protected] |
+-----+----------+----------+---------+
2 rows in set (0.00 sec)mysql>