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)'

Answer 1

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.