Skip to content

Instantly share code, notes, and snippets.

@rafaelurben

rafaelurben/MariaDB-char32-to-uuid-converter-generator.sql

Last active December 12, 2025 14:46
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rafaelurben/59c0b9887026891630e5859449a98306 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rafaelurben/59c0b9887026891630e5859449a98306 to your computer and use it in GitHub Desktop.
Download ZIP
MariaDB char(32) to native uuid migration generator
Raw
MariaDB-char32-to-uuid-converter-generator.sql
-- ============================================================================
-- UUID migration SQL GENERATOR for MariaDB 10.11.14
-- Produces statements to migrate UUID-like CHAR(32) columns to native UUID.
-- Usage: run this script in the target database. It will OUTPUT SQL statements
-- which you should REVIEW and run in the order indicated below.
--
-- This can be particularly helpful when migrating Django apps, as Django used
-- to use char(32) for UUIDFields but switched to uuid in django 5.0.
--
-- NOTE: Pay attention if you have non-uuid char(32) columns!
--
-- Created by rafaelurben and ChatGPT
-- ============================================================================
-- 0) Parameters: change if you need a different database than DATABASE()
SET @target_db = DATABASE();
-- ========================================================================
-- A) Identify candidate columns: CHAR(32)
-- Result: list of table/column IS_NULLABLE and type info
-- ========================================================================
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
COLUMN_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @target_db
AND (DATA_TYPE = 'char' AND CHARACTER_MAXIMUM_LENGTH = 32)
ORDER BY TABLE_NAME, ORDINAL_POSITION;
-- ========================================================================
-- B) Generate ADD COLUMN statements (adds <col>_tmp UUID NULL/NOT NULL)
-- These statements are safe to run repeatedly; they check for existing tmp col.
-- ========================================================================
SELECT CONCAT(
'ALTER TABLE `', TABLE_NAME, '` ADD COLUMN `', COLUMN_NAME, '_tmp` UUID ',
(CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END), ';'
) AS add_tmp_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @target_db
AND (DATA_TYPE = 'char' AND CHARACTER_MAXIMUM_LENGTH = 32)
ORDER BY TABLE_NAME, COLUMN_NAME;
-- ========================================================================
-- C) Generate UPDATE statements to populate tmp columns safely
-- Handles char(32). Produces per-column UPDATE.
-- ========================================================================
SELECT CONCAT(
'UPDATE `', TABLE_NAME, '`\n',
'SET `', COLUMN_NAME, '_tmp` = (\n',
' CASE\n',
' WHEN `', COLUMN_NAME, '` IS NULL OR `', COLUMN_NAME, '` = \'\' THEN NULL\n',
' WHEN LENGTH(`', COLUMN_NAME, '`) = 32 AND `', COLUMN_NAME,
'` REGEXP ''^[0-9A-Fa-f]{32}$'' THEN (\n',
' LOWER(CONCAT(\n',
' SUBSTR(`', COLUMN_NAME, '`,1,8),''-'',\n',
' SUBSTR(`', COLUMN_NAME, '`,9,4),''-'',\n',
' SUBSTR(`', COLUMN_NAME, '`,13,4),''-'',\n',
' SUBSTR(`', COLUMN_NAME, '`,17,4),''-'',\n',
' SUBSTR(`', COLUMN_NAME, '`,21,12)\n',
' ))\n',
' )\n',
' ELSE NULL\n',
' END\n',
')\n',
'WHERE 1;'
) AS populate_tmp_sql
FROM (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @target_db
AND (DATA_TYPE = 'char' AND CHARACTER_MAXIMUM_LENGTH = 32)) AS cols
ORDER BY TABLE_NAME, COLUMN_NAME;
-- ========================================================================
-- D) Generate verification check
-- ========================================================================
SELECT CONCAT(
GROUP_CONCAT(
CONCAT(
'SELECT ''', TABLE_NAME, '.', COLUMN_NAME, ''' AS column_ident, ',
'COUNT(*) AS total_rows, ',
'SUM(CASE WHEN `', COLUMN_NAME, '` IS NOT NULL AND `', COLUMN_NAME, '` <> '''' AND `',
COLUMN_NAME, '_tmp` IS NULL THEN 1 ELSE 0 END) AS conversion_failures, ',
'GROUP_CONCAT(CASE WHEN `', COLUMN_NAME, '` IS NOT NULL AND `', COLUMN_NAME,
'` <> '''' AND `', COLUMN_NAME, '_tmp` IS NULL ',
'THEN CONCAT(''('', `', COLUMN_NAME, '`, '','', `', COLUMN_NAME,
'_tmp`, '')'') END SEPARATOR ''; '') AS sample_failed_values ',
'FROM `', TABLE_NAME, '`'
)
SEPARATOR ' UNION ALL '
),
';'
) AS full_verification_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @target_db
AND DATA_TYPE = 'char'
AND CHARACTER_MAXIMUM_LENGTH = 32;
-- ========================================================================
-- E) Generate statements to DROP keys and indices reference any of these columns.
-- ========================================================================
-- E.1) Drop Foreign keys
SELECT DISTINCT CONCAT(
'ALTER TABLE `', kcu.TABLE_SCHEMA, '`.`', kcu.TABLE_NAME, '` DROP FOREIGN KEY `',
kcu.CONSTRAINT_NAME, '`;'
) AS drop_fk_sql
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE kcu.REFERENCED_TABLE_SCHEMA = @target_db
AND EXISTS (
-- ensure this constraint references a column that we plan to migrate
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = @target_db
AND c.TABLE_NAME = kcu.REFERENCED_TABLE_NAME
AND c.COLUMN_NAME = kcu.REFERENCED_COLUMN_NAME
AND (DATA_TYPE = 'char' AND CHARACTER_MAXIMUM_LENGTH = 32))
ORDER BY drop_fk_sql;
-- E.2) Drop Primary keys
SELECT DISTINCT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` DROP PRIMARY KEY;') AS drop_pk_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND DATA_TYPE = 'char'
AND CHARACTER_MAXIMUM_LENGTH = 32
AND COLUMN_KEY = 'PRI';
-- E.3) Drop Unique indexes
SELECT DISTINCT CONCAT(
'ALTER TABLE `', tc.TABLE_SCHEMA, '`.`', tc.TABLE_NAME, '` DROP INDEX `', tc.CONSTRAINT_NAME,
'`;'
) AS drop_unique_sql
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'UNIQUE'
AND tc.CONSTRAINT_SCHEMA = DATABASE()
AND EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = DATABASE()
AND c.TABLE_NAME = kcu.TABLE_NAME
AND c.COLUMN_NAME = kcu.COLUMN_NAME
AND c.DATA_TYPE = 'char'
AND c.CHARACTER_MAXIMUM_LENGTH = 32)
ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_NAME;
-- ========================================================================
-- F) Generate final ALTER statements to drop old column and rename tmp -> original
-- These are the statements you run AFTER verification and AFTER DROP FK statements.
-- ========================================================================
SELECT CONCAT(
'ALTER TABLE `', TABLE_NAME, '` DROP COLUMN `', COLUMN_NAME, '`, CHANGE COLUMN `', COLUMN_NAME,
'_tmp` `', COLUMN_NAME, '` UUID ',
(CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END),
';'
) AS final_swap_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @target_db
AND (DATA_TYPE = 'char' AND CHARACTER_MAXIMUM_LENGTH = 32)
ORDER BY TABLE_NAME, COLUMN_NAME;
-- ========================================================================
-- G) Generate statements to recreate keys and indices .
-- ========================================================================
-- G.1) Generate statements to RECREATE UNIQUE constraints (grouping multi-column constraints)
-- TODO: This might leave out columns that are not fks but are part of the unique constraint. Also, columns might be duplicated.
SELECT CONCAT(
'ALTER TABLE `', tc.TABLE_SCHEMA, '`.`', tc.TABLE_NAME, '` ADD UNIQUE `', tc.CONSTRAINT_NAME, '` (',
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY ORDINAL_POSITION SEPARATOR ', '),
');'
) AS create_unique_sql
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'UNIQUE'
AND tc.CONSTRAINT_SCHEMA = DATABASE()
AND EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = DATABASE()
AND c.TABLE_NAME = kcu.TABLE_NAME
AND c.COLUMN_NAME = kcu.COLUMN_NAME
AND c.DATA_TYPE = 'char'
AND c.CHARACTER_MAXIMUM_LENGTH = 32)
GROUP BY tc.CONSTRAINT_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_NAME
ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_NAME;
-- G.2) Generate statements to RECREATE primary keys (grouping multi-column PKs)
SELECT DISTINCT CONCAT(
'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ADD PRIMARY KEY (',
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY ORDINAL_POSITION SEPARATOR ', '),
');'
) AS create_pk_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND DATA_TYPE = 'char'
AND CHARACTER_MAXIMUM_LENGTH = 32
AND COLUMN_KEY = 'PRI'
GROUP BY TABLE_SCHEMA, TABLE_NAME;
-- G.3) Now produce CREATE (recreate) FK statements (grouping multi-column constraints)
SELECT CONCAT(
'ALTER TABLE `', kcu.TABLE_SCHEMA, '`.`', kcu.TABLE_NAME, '` ADD CONSTRAINT `', kcu.CONSTRAINT_NAME,
'` FOREIGN KEY (',
GROUP_CONCAT(CONCAT('`', kcu.COLUMN_NAME, '`') ORDER BY kcu.ORDINAL_POSITION SEPARATOR ', '),
') REFERENCES `', kcu.REFERENCED_TABLE_SCHEMA, '`.`', kcu.REFERENCED_TABLE_NAME, '` (',
GROUP_CONCAT(CONCAT('`', kcu.REFERENCED_COLUMN_NAME, '`') ORDER BY kcu.ORDINAL_POSITION SEPARATOR ', '),
') ON UPDATE ', rc.UPDATE_RULE, ' ON DELETE ', rc.DELETE_RULE, ';'
) AS recreate_fk_sql
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE kcu.REFERENCED_TABLE_SCHEMA = @target_db
AND EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = @target_db
AND c.TABLE_NAME = kcu.REFERENCED_TABLE_NAME
AND c.COLUMN_NAME = kcu.REFERENCED_COLUMN_NAME
AND (DATA_TYPE = 'char' AND CHARACTER_MAXIMUM_LENGTH = 32))
GROUP BY kcu.CONSTRAINT_NAME, kcu.TABLE_SCHEMA, kcu.TABLE_NAME, rc.UPDATE_RULE, rc.DELETE_RULE
ORDER BY recreate_fk_sql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment