MySQL Tips and Tricks

Regexes in WHERE

# Normalise ECN123.. => EN123..
UPDATE MyValues
   SET Content = replace(Content, 'ECN', 'EN')
 WHERE Content REGEXP '^ECN[[:digit:]]{8}$'

Remove leading digits

# Assume leading digits in a street name are unnecessary
# Re-run times to get an
#   "3000 West 13th Ave' => 'West 13th Ave'
#   "#12 - 3000 2nd Ave' => '2nd Ave'
#
UPDATE Addresses
   SET street = TRIM( SUBSTR( street, INSTR(street, ' ') ) )
 WHERE street REGEXP '^[-#[:digit:]]+ [[:alnum:]]'

Convert MyISAM to InnoDB

# Check InnoDB is enabled
SHOW VARIABLES LIKE "have_innodb";
SHOW ENGINES;

# Find all tables to convert
SELECT CONCAT("alter table ",TABLE_SCHEMA,".",TABLE_NAME," engine=innodb;")
  FROM information_schema.tables 
 WHERE ENGINE = "MyISAM"
   AND TABLE_SCHEMA NOT IN ("mysql", "information_schema");

# Change default engine
# edit /etc/my.cnf "default_storage_engine = InnoDB"
SET storage_engine = "InnoDB";