How to set the column default value to generate random 6 digit number

Question

I want to generate 6 digit random number to a column when a new row added to the table. I tried the below query to set Default value to that column

ALTER TABLE test_table 
    ADD COLUMN test_column int(10) NOT NULL DEFAULT LPAD(FLOOR(RAND() * 999999.99), 6, 1);

It is not working on MySQL 5.6 and returning the following error

Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LPAD(FLOOR(RAND() * 999999.99), 6, 1)'

MYSQL is my main DB. We are using MYSQL in the prod but for the dev work we are using MariaDB — Sathya, Apr 15 '21 at 00:32
What do you mean by "not working?" What error is being thrown? Have you tried anything else? Are you expecting this same syntax to work between two different DBMSs? — Jacob Barnes, Apr 15 '21 at 00:53
The way I see it, the choice to have two different DB for the dev and prod is never a good choice. Of course, MariaDB is a fork of MySQL but there are still differences in what operation they can perform.. and this is one of it. I've tested MySQL 5.6, 5.7 and 8.0 versions and all of them failed run your syntax; all returning the same `error 1064`. — FanoFN, Apr 15 '21 at 01:01
Alternatively, [you can use trigger instead](https://stackoverflow.com/questions/35380406/how-to-create-random-number-for-a-column-in-mysql-with-default-constraint) — FanoFN, Apr 15 '21 at 01:06

Bill Karwin · Answer 1 · 2021-04-15T15:41:11.157

MySQL does not support expressions as a column DEFAULT until version 8.0.13. See https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

mysql> create table mytable ( n varchar(6) default (lpad(rand() * 1000000, 6, '0')));

mysql> insert into mytable () values ();
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytable;
+--------+
| n      |
+--------+
| 573663 |
+--------+

MariaDB supports similar expression defaults in version 10.2.1. See https://mariadb.com/kb/en/create-table/#default-column-option

The alternative that should work in earlier versions of both databases is to define a trigger that sets the random value by default if the column's value is NULL.

mysql> create table mytable ( n varchar(6) );

mysql> create trigger rand_lpad_ins before insert on mytable 
       for each row set NEW.n = COALESCE(NEW.n, lpad(rand() * 1000000, 6, '0'));

mysql> insert into mytable () values ();

mysql> select * from mytable;
+--------+
| n      |
+--------+
| 422870 |
+--------+

P.S.: It's a bad idea to use a different database product in development versus production. You risk using features in MariaDB during development that don't work the same in MySQL. You can't test them until you deploy to production. That's a costly kind of surprise, because when you discover it, you may have to go rework a lot of what you did in development. MariaDB and MySQL started with common code in 2010, but they have been diverging since then. You should use the same brand and version of database (and any other software) that you will use in production.

Unless I'm misunderstanding your answer, MariaDB does support expressions in default values: `The DEFAULT clause was enhanced in MariaDB 10.2.1. Some enhancements include: The DEFAULT clause can now be used with an expression or function.`. The same SQL you used for MySQL 8.0 seems to work at least with MariaDB 10.3. — markusjm, Apr 15 '21 at 08:48
@markusjm, You're right, my mistake. I have edited my answer above to be more accurate. This supports my postscript about using the same version in development and production, because if the team uses MariaDB 10.2 or greater in development, they might try to use expression defaults, which won't work when they deploy to production on MySQL 5.6. — Bill Karwin, Apr 15 '21 at 15:41
Absolutely, using the same version you use in production for your development is critical as even a difference in the patch version can cause wildly different behavior due to bugs and improvements. — markusjm, Apr 16 '21 at 06:04

How to set the column default value to generate random 6 digit number

1 Answers1