MySQL - 使其他列成为自动增量列的函数

Question

This question has been asked but never clearly answered (as far as I am aware): - I want to give my users a unique temporary username. My idea is to make their username a function of the id column as follows: -

id username
  1 user1
  2 user2
  . . . 
  967 user967
  968 user968

I can do this with an INSERT, a SELECT and an UPDATE, but is there a more efficient way? Can it be done with just an INSERT?

Answer 1

It can be achieved with a BEFORE INSERT trigger.

First you need to read current AUTO_INCREMENT value to be assigned and use it to generate the temporary username.

Example:

DELIMITER //

DROP TRIGGER IF EXISTS trg_bi_tbl_name //

CREATE TRIGGER trg_bi_tbl_name
       BEFORE INSERT ON tbl_name
       FOR EACH ROW 
BEGIN
    DECLARE _user_id INT DEFAULT 0;

    IF NEW.username IS NOT NULL AND LENGTH( TRIM( NEW.username ) ) = 0 THEN
         SELECT AUTO_INCREMENT INTO _user_id
           FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_NAME = 'tbl_name'
            AND TABLE_SCHEMA = DATABASE();

         SET NEW.username = CONCAT( 'user', _user_id );
    END IF;
END;

//

DELIMITER ;

As you mentioned it is for temporary username suggestion, I don't think you need a BEFORE UPDATE trigger.