Modify a auto_increment id column in mysql to accept a 5 digit random number instead

By: Peter in MySQL Tutorials on 2023-03-27

You can modify the column definition to remove the auto-increment property and then set a default value of a randomly generated 5-digit number.

Here are the steps you can follow:

Alter the table to drop the auto-increment property of the column:

ALTER TABLE my_table MODIFY COLUMN id INT;

This will remove the auto-increment property from the id column.

Alter the table again to set the default value for the id column:

ALTER TABLE my_table ALTER COLUMN id SET DEFAULT (FLOOR(RAND() * 90000) + 10000);

This will set a default value for the id column that is a randomly generated 5-digit number between 10000 and 99999.

Note that this default value will only be applied to new rows that are inserted into the table.

Add a unique constraint to the id column to ensure that each value is unique:

ALTER TABLE my_table ADD CONSTRAINT unique_id UNIQUE (id);

This will add a unique constraint to the id column, which will ensure that each value is unique.

Optionally, you can create a trigger to generate a random value for the id column whenever a new row is inserted:

CREATE TRIGGER my_trigger BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
  SET NEW.id = FLOOR(RAND() * 90000) + 10000;
END;

This will create a trigger that sets the id column to a random value between 10000 and 99999 whenever a new row is inserted into the table.

Note that modifying a column in a production database can be a risky operation, so it is recommended that you make a backup of your data before making any changes. Also, be sure to test your changes thoroughly to ensure that they do not cause any unintended side effects.

Previous
<< Windows cannot access the specified device, path or file. You may not have the appropriate permissions to access them.

Next
sql if null then 0 >>