CODEWITHSUNDEEP
mysqlinsertindexingunique
Goce Ribeski
Goce Ribeski

Reputation: 1372

MySQL insert unique record by index of columns

I have a table:

table user(
  id_user,
  userName,
  email,
  primary key(id_user)
);

I added unique index on it:

alter table user add unique index(userName, email);

Now I have two indexs on the table:

Index:
Keyname     Unique      Field   
PRIMARY     Yes         id_user
userName    Yes         userName, email

The task is to find the MySQL statement for fastest way to insert new unique record. Statement should return Id_user of the new or existent record.

I'm considering these 2 options, and don't know which is better or is there some third better way to do this?:

1.

INSERT INTO `user` (`userName`, `email`)
VALUES (u1,'[email protected]' )
ON DUPLICATE KEY Ignore

Q: Where in this statement should be specified that the required KEY for unique inserts is Keyname = uesrName?

2.

IF EXISTS(SELECT `userName`, `email` FROM user WHERE `userName` = u1 AND `email` = [email protected])
BEGIN

END
ELSE
BEGIN
      INSERT INTO user(`userName`, `email`)
      VALUES (u1, [email protected]);
END IF;

Q: In this statement - how the index with Keyname = userName should be taken in consideration?

Thanks!

Upvotes: 2

Views: 1120

Answers (1)

Johan
Johan

Reputation: 76753

The only way to get data out of a table in MySQL is to select.

DELIMITER $$

CREATE FUNCTION ForceUser(pUsername varchar(255), pEmail varchar(255)) 
RETURNS integer
BEGIN
  DECLARE MyId INTEGER;

  /*First do a select to see if record exists:*/
  /*because (username,email) is a unique key this will return null or a unique id.*/
  SELECT id INTO MyId FROM user 
  WHERE username = pUsername 
    AND email = pEmail;
  IF MyId IS NULL THEN 
    /*If not then insert the record*/
    INSERT INTO user (username, email) VALUES (pUserName,pEmail);
    SELECT LAST_INSERT_ID() INTO MyId;
  END IF;
  RETURN MyID;
END $$

DELIMITER ;

Q: Where in this statement should be specified that the required KEY for unique inserts is Keyname = uesrName?

A: MySQL already knows this, because that information is part of the table definition.

See: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

Upvotes: 1

Related Questions