Laravel migration to backfill uuids in every table

Question

I am attempting to create a database migration (MySQL) to backfill uuids in all tables (roughly 80 tables, some with 130k+ rows). To do this directly in MySQL, I can go table by table and run:

UPDATE <TABLE_NAME> SET uuid = (SELECT md5(UUID()))';

which does add a unique UUID per row. If I loop through all tables and run a DB facade statement per table:

class BackfillUuidsIntoAllTables extends Migration
{
    protected $dbName;
    protected $tables;

    public function __construct()
    {
        $this->dbName = config('database.connections.' . config('database.default') . '.database');
        $this->tables = DB::select('SHOW TABLES');
    }

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        foreach ($this->tables as $table) {
            Schema::table($table->{'Tables_in_' . $this->dbName}, function ($table) {
                $tableName = $table->getTable();

                DB::statement("UPDATE $tableName SET uuid = (SELECT md5(UUID()))");
        }
    }
}

it executes quickly, but uses the same uuid for an entire table. Am I missing something in order to run this so every row actually gets a unique uuid?

Answer 1

If you add the row's unique id to the md5() call, it should generate a different string per row. Also, the nested SELECT doesn't seem necessary.

This'll do the trick:

UPDATE
    <tablename>
SET
    `uuid` = MD5(CONCAT(UUID(), `id`))