MySQL通过列索引插入唯一记录

发布于 2024-12-09 01:21:01 字数 1337 浏览 0 评论 0原文

我有一个表：

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

我在上面添加了唯一索引：

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

现在我在表上有两个索引：

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

任务是找到 MySQL 语句以最快的方式插入新的唯一记录。语句应返回新记录或现有记录的 Id_user。

我正在考虑这 2 个选项，但不知道哪个更好，或者是否有第三种更好的方法来做到这一点？：

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

问：在该语句中应指定唯一插入所需的 KEY 为 Keyname = uesrName ？

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;

问：在这个语句中——Keyname = userName 的索引应该如何考虑？

谢谢！

原文

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?:

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?

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

慵挽 2024-12-16 01:21:01

从 MySQL 的表中获取数据的唯一方法是选择。

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 ;

问：在该语句中应在何处指定唯一插入所需的 KEY 为 Keyname = uesrName？

答：MySQL 已经知道这一点，因为该信息是表定义的一部分。

请参阅： http://dev.mysql.com/doc /refman/5.0/en/getting-unique-id.html

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 ;