MYSQL random generated id & LOAD DATA LOCAL INFILE

is there a way to use LOAD DATA LOCAL INFILE & create a random generated id rather than using the auto increment function?

I have tried using MySQL's UUID() but it only produces the same id for each row

$sql = "LOAD DATA LOCAL INFILE \"" . $data['full_path'] . "\" REPLACE INTO TABLE as  " . 'FIELDS TERMINAT开发者_JAVA百科ED BY "\\n"' . 'LINES TERMINATED BY "\\r" IGNORE 1 LINES (contact_id, company) SET contact_id=UUID()';

Cheers

Have a look at the user documentation and user variables. I think you can get it working with them: http://dev.mysql.com/doc/refman/5.5/en/load-data.html

Are you sure that UUID() isn't putting something different in each row? I've just tried it and it seems to work fine. It's only the first group of characters that seem to change each time though.

It's simple to generate a random number.

select floor(1 + (rand() * 5))

For example this select generate a number between 1 and 5, that, in your case, becomes

set contact_id = floor(1 + (rand() * 5))

I don't know if you need a unique id for each row. In that case I don't know, using load data, how to produce unique ids.

As alternative you can make an update query in a second moment

set @i := 0;
update table set contact_id = (@i := @i + 1) order by rand()