Chris92 Chris92 - 4 years ago 146
SQL Question

Adding new record in MySQL with random values except ID

Is there a possibility in MySQL to fill specific number of columns in record by random values, except of ID (which should be auto-incremented)?

Something like that:

INSERT INTO 'table_name' ('ID', column1, column2, ...)
VALUES (<auto-increment> , random-value1, random-value2, ...);


I am testing something with database by removing few records, and after that i have to manually create few again. I'd like creating new records to be more automatic.

Answer Source

Yes. The ID has to be declared as auto_increment and not listed in the insert statment. Rand() returns a float between 0 and 1.

create table my_table(
  id int primary key auto_increment,
  val1 float,
  val2 float
);

insert into my_table(
  val1,
  val2
) values (
  rand(),
  rand()
);

select * from my_table;

SQL Fiddle
MySQL 5.7 rand() function documentation

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download