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.
Add Comment
This policy contains information about your privacy. By posting, you are declaring that you understand this policy:
- Your name, rating, website address, town, country, state and comment will be publicly displayed if entered.
- Aside from the data entered into these form fields, other stored data about your comment will include:
- Your IP address (not displayed)
- The time/date of your submission (displayed)
- Your email address will not be shared. It is collected for only two reasons:
- Administrative purposes, should a need to contact you arise.
- To inform you of new comments, should you subscribe to receive notifications.
- A cookie may be set on your computer. This is used to remember your inputs. It will expire by itself.
This policy is subject to change at any time and without notice.
These terms and conditions contain rules about posting comments. By submitting a comment, you are declaring that you agree with these rules:
- Although the administrator will attempt to moderate comments, it is impossible for every comment to have been moderated at any given time.
- You acknowledge that all comments express the views and opinions of the original author and not those of the administrator.
- You agree not to post any material which is knowingly false, obscene, hateful, threatening, harassing or invasive of a person's privacy.
- The administrator has the right to edit, move or remove any comment for any reason and without notice.
Failure to comply with these rules may result in being banned from submitting further comments.
These terms and conditions are subject to change at any time and without notice.
- Data Science
- Android
- React Native
- AJAX
- ASP.net
- C
- C++
- C#
- Cocoa
- Cloud Computing
- HTML5
- Java
- Javascript
- JSF
- JSP
- J2ME
- Java Beans
- EJB
- JDBC
- Linux
- Mac OS X
- iPhone
- MySQL
- Office 365
- Perl
- PHP
- Python
- Ruby
- VB.net
- Hibernate
- Struts
- SAP
- Trends
- Tech Reviews
- WebServices
- XML
- Certification
- Interview
categories
Related Tutorials
Use a dynamic table name in a SQL Server SELECT statement
Finding slow queries in MySQL - Enable slow query log.
mysqldumpslow in MySQL - Summarize slow query log.
Sample my.cnf (my.ini) for MySQL with 1GB RAM
Modify a auto_increment id column in mysql to accept a 5 digit random number instead
Changing the Structure of an Existing Table in MySQL
Inserting Data into Tables in MySQL
Querying the Database in MySQL
Comments