Mysql Question posted in
The official MySQL documentation can be found here.

Creating a Auto Generation ID with Prefix in MySQL Without Using Trigger

Creating a Auto Generation ID with Prefix in MySQL Without Using Trigger
I need Creating a Employee Id with Prefix and Without Using Trigger in MySQL.

Tags:

2

Answers


  1. 0

    To create an auto-generated employee ID with a prefix in MySQL without using triggers, you can follow one of the approaches below:

    1. Using AUTO_INCREMENT

    You can use the AUTO_INCREMENT feature in MySQL to automatically generate unique IDs for your employees.

    CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_prefix VARCHAR(10) NOT NULL,
    employee_number INT
);

-- Insert a row (ID will be automatically generated)
INSERT INTO employees (employee_prefix) VALUES ('EMP');

-- Get the generated ID
SELECT LAST_INSERT_ID();
    1. Computed Column

    You can create a computed column that combines the prefix and an auto-incremented value.

    CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_number INT,
    employee_id AS CONCAT('EMP', LPAD(employee_number, 5, '0')) PERSISTENT
);

-- Insert a row (ID will be automatically generated)
INSERT INTO employees (employee_number) VALUES (1);

-- Get the generated employee ID
SELECT employee_id FROM employees WHERE id = LAST_INSERT_ID();

    Choose the approach that best fits your requirements, and adjust the table and column names as needed. Remember to replace ‘EMP’ with your desired prefix.

    Login or Signup to reply.
  2. 0

    The first paradigm looks fine to me. On the second one i think, that’s not a correct syntax for MySQL.

    However to use generated columns, you can choose between two opportunities: Virtual / Stored.

    MySQL : 15.1.20.8 CREATE TABLE and Generated Columns

    My read is, the example (on using the above described example, part 2) will use stored columns:

    CREATE TABLE `employees` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `employee_number` INT NULL,
  `employee_id` VARCHAR(8) GENERATED ALWAYS AS (CONCAT('EMP', LPAD(employee_number, 5, '0'))) STORED,
  PRIMARY KEY (`id`));

INSERT INTO `employees` (`employee_number`) VALUES ('1516');

select * from employees;

+----+-----------------+-------------+
| id | employee_number | employee_id |
+----+-----------------+-------------+
|  1 |            1516 | EMP01516    |
+----+-----------------+-------------+
1 row in set (0.001 sec)

    This is a very simple example so far. For the further development, i strongly recommend to check (for your intention) the datatypes and boundaries.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search