Friday, May 10, 2013

Regex to replace charset in MySQL dump.

converting all our databases to charset utf8mb4 and collation utf8_general_ci.
Exported all structure using mysqldump from my last post.
Now for ease of completing over 5000 table I wanted to use regex, this is what I got...

/////////////////// USING Komodo Edit!! //////////////////////////////////////////


---- COLLATE STATEMENTS ----
FIND: COLLATE([ =]).*?([ ;])
REPLACE: COLLATE\1utf8_general_ci\2

---- COLLATE STATEMENTS 2 ----
FIND: collation_connection      = [^ ]*
REPLACE: collation_connection      = utf8_general_ci

---- CHARSET STATEMENTS ----
FIND: CHARSET([ =]).*?([ ;])
REPLACE: CHARSET\1utf8mb4\2
---- CHARSET STATEMENTS 2 ----
FIND: character_set_client = [^ ]*
REPLACE: character_set_client = utf8mb4
---- CHARSET STATEMENTS 3 ----
FIND: CHARACTER SET [^ ]*
REPLACE: CHARACTER SET utf8mb4
---- CHARSET STATEMENTS 3 ----
FIND: (character_set_[^ ]*[ ]*)= [a-z0-9_]+
REPLACE: \1     = utf8mb4

---- DEFINER STATEMENTS ----
FIND: DEFINER=`.*`@`.*`
REPLACE: DEFINER=`myuser`@`localhost`

---- ENGINE STATEMENTS ----
FIND: ENGINE=[^ ]*
REPLACE: ENGINE=InnoDB

---- ENGINE STATEMENTS ----
FIND: ENGINE=[^ ]*
REPLACE: ENGINE=InnoDB

other things I changed


----- standardize column lengths -----
FIND: (`columnprefix_.*`) varchar\(.*\)
REPLACE: \1 varchar(36)

----- standardize column lengths 2 -----
FIND: (`columname`) varchar\(.*\)
REPLACE: \1 varchar(36)