Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Convert all columns (and data) in a Contented MySQL database into UTF-8
ALTER DATABASE contented1917 CHARACTER SET utf8;
ALTER TABLE content CHARACTER SET utf8;
ALTER TABLE content_category CHARACTER SET utf8;
ALTER TABLE content_history CHARACTER SET utf8;
ALTER TABLE content_list CHARACTER SET utf8;
ALTER TABLE content_list_field CHARACTER SET utf8;
ALTER TABLE content_metadata CHARACTER SET utf8;
ALTER TABLE content_note CHARACTER SET utf8;
ALTER TABLE content_relation CHARACTER SET utf8;
ALTER TABLE content_relation_type CHARACTER SET utf8;
ALTER TABLE content_relation_version CHARACTER SET utf8;
ALTER TABLE content_relationship_category CHARACTER SET utf8;
ALTER TABLE content_site CHARACTER SET utf8;
ALTER TABLE content_structure CHARACTER SET utf8;
ALTER TABLE content_type CHARACTER SET utf8;
ALTER TABLE content_type_user CHARACTER SET utf8;
ALTER TABLE content_user CHARACTER SET utf8;
ALTER TABLE content_version CHARACTER SET utf8;
ALTER TABLE contented_access_right CHARACTER SET utf8;
ALTER TABLE contented_phpupdate CHARACTER SET utf8;
ALTER TABLE contented_user CHARACTER SET utf8;
ALTER TABLE contented_user_access_right CHARACTER SET utf8;
ALTER TABLE setting CHARACTER SET utf8;
ALTER TABLE sys_session CHARACTER SET utf8;
-- content
alter table content
change Status Status VARBINARY(20);
alter table content
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_category
alter table content_category
change Name Name VARBINARY(56),
change Status Status VARBINARY(20);
alter table content_category
change Name Name VARCHAR(56) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_history
alter table content_history
change Action Action VARBINARY(255),
change Status Status VARBINARY(20);
alter table content_history
change Action Action VARCHAR(255) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_list
alter table content_list
change Name Name VARBINARY(20),
change Description Description VARBINARY(128),
change Status Status VARBINARY(20);
alter table content_list
change Name Name VARCHAR(20) CHARACTER SET utf8,
change Description Description VARCHAR(128) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_list_field
alter table content_list_field
change Field Field VARBINARY(255),
change Label Label VARBINARY(100),
change Status Status VARBINARY(20);
alter table content_list_field
change Field Field VARCHAR(255) CHARACTER SET utf8,
change Label Label VARCHAR(100) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_metadata
alter table content_metadata
change TagName TagName VARBINARY(50),
change Value Value VARBINARY(255),
change `Path` `Path` VARBINARY(255),
change DataType DataType VARBINARY(20),
change Status Status VARBINARY(20);
alter table content_metadata
change TagName TagName VARCHAR(50) CHARACTER SET utf8,
change Value Value VARCHAR(255) CHARACTER SET utf8,
change `Path` `Path` VARCHAR(255) CHARACTER SET utf8,
change DataType DataType VARCHAR(20) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_note
alter table content_note
change Note Note VARBINARY(255),
change Status Status VARBINARY(20);
alter table content_note
change Note Note VARCHAR(255) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_relation
alter table content_relation
change MetaDataXML MetaDataXML BLOB,
change Status Status VARBINARY(20);
alter table content_relation
change MetaDataXML MetaDataXML TEXT CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_relation_type
alter table content_relation_type
change Name Name VARBINARY(56),
change Status Status VARBINARY(20);
alter table content_relation_type
change Name Name VARCHAR(56) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_relation_version
alter table content_relation_version
change Status Status VARBINARY(20);
alter table content_relation_version
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_relationship_category
alter table content_relationship_category
change Name Name VARBINARY(50),
change Description Description VARBINARY(255),
change RelCategoryType RelCategoryType VARBINARY(20),
change Status Status VARBINARY(20);
alter table content_relationship_category
change Name Name VARCHAR(50) CHARACTER SET utf8,
change Description Description VARCHAR(255) CHARACTER SET utf8,
change RelCategoryType RelCategoryType VARCHAR(20) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_site
alter table content_site
change Name Name VARBINARY(255),
change IconFileName IconFileName VARBINARY(255),
change Status Status VARBINARY(20);
alter table content_site
change Name Name VARCHAR(255) CHARACTER SET utf8,
change IconFileName IconFileName VARCHAR(255) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_structure
alter table content_structure
change Name Name VARBINARY(255),
change Introduction Introduction VARBINARY(255),
change Status Status VARBINARY(20);
alter table content_structure
change Name Name VARCHAR(255) CHARACTER SET utf8,
change Introduction Introduction VARCHAR(255) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_type
alter table content_type
change Name Name VARBINARY(56),
change Description Description VARBINARY(128),
change RelationsTabName RelationsTabName VARBINARY(30),
change `Schema` `Schema` BLOB,
change EditorXSLT EditorXSLT MEDIUMBLOB,
change BranchIconFileName BranchIconFileName VARBINARY(255),
change LeafIconFileName LeafIconFileName VARBINARY(255),
change Status Status VARBINARY(20),
change DefaultPage DefaultPage VARBINARY(50);
alter table content_type
change Name Name VARCHAR(56) CHARACTER SET utf8,
change Description Description VARCHAR(128) CHARACTER SET utf8,
change RelationsTabName RelationsTabName VARCHAR(30) CHARACTER SET utf8,
change `Schema` `Schema` TEXT CHARACTER SET utf8,
change EditorXSLT EditorXSLT MEDIUMTEXT CHARACTER SET utf8,
change BranchIconFileName BranchIconFileName VARCHAR(255) CHARACTER SET utf8,
change LeafIconFileName LeafIconFileName VARCHAR(255) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8,
change DefaultPage DefaultPage VARCHAR(50) CHARACTER SET utf8;
-- content_type_user
alter table content_type_user
change Type Type VARBINARY(20),
change Status Status VARBINARY(20);
alter table content_type_user
change Type Type VARCHAR(20) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_user
alter table content_user
change Type Type VARBINARY(20),
change Status Status VARBINARY(20);
alter table content_user
change Type Type VARCHAR(20) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- content_version
-- A limitation of MySQL:
-- If you get an error: "Column 'column_name' cannot be part of FULLTEXT index"
-- here's why:
-- All columns of a FULLTEXT index must have not only the same character set but also the same collation.
-- http://stackoverflow.com/questions/653076/mysql-error-column-columnname-cannot-be-part-of-fulltext-index
ALTER TABLE content_version DROP INDEX FTContent_Version;
alter table content_version
change Title Title VARBINARY(128),
change Description Description VARBINARY(255),
change ShortURL ShortURL VARBINARY(100),
change ContentXML ContentXML BLOB,
change MetaDataXML MetaDataXML BLOB,
change DisplayPage DisplayPage VARBINARY(50),
change Status Status VARBINARY(20);
alter table content_version
change Title Title VARCHAR(128) CHARACTER SET utf8,
change Description Description VARCHAR(128) CHARACTER SET utf8,
change ShortURL ShortURL VARCHAR(100) CHARACTER SET utf8,
change ContentXML ContentXML TEXT CHARACTER SET utf8,
change MetaDataXML MetaDataXML TEXT CHARACTER SET utf8,
change DisplayPage DisplayPage VARCHAR(50) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- restore the index
ALTER TABLE content_version ADD FULLTEXT INDEX `FTContent_Version` (Title, Description, ContentXML);
-- contented_access_right
alter table contented_access_right
change Name Name VARBINARY(56),
change Description Description VARBINARY(128),
change Status Status VARBINARY(20);
alter table contented_access_right
change Name Name VARCHAR(56) CHARACTER SET utf8,
change Description Description VARCHAR(128) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- contented_phpupdate
alter table contented_phpupdate
change FileName FileName VARBINARY(255),
change ObjectName ObjectName VARBINARY(255),
change UpdateMethodName UpdateMethodName VARBINARY(255),
change Status Status VARBINARY(20);
alter table contented_phpupdate
change FileName FileName VARCHAR(255) CHARACTER SET utf8,
change ObjectName ObjectName VARBINARY(255),
change UpdateMethodName UpdateMethodName VARCHAR(255) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- contented_user
alter table contented_user
change user_name user_name VARBINARY(20),
change email email VARBINARY(128),
change `password` `password` VARBINARY(128),
change remote_addr remote_addr VARBINARY(128),
change confirm_hash confirm_hash VARBINARY(128),
change surname surname VARBINARY(50),
change forename forename VARBINARY(50),
change title title VARBINARY(10),
change MultiSiteNavState MultiSiteNavState VARBINARY(255),
change status status VARBINARY(20);
alter table contented_user
change user_name user_name VARCHAR(20) CHARACTER SET utf8,
change email email VARBINARY(128),
change `password` `password` VARCHAR(128) CHARACTER SET utf8,
change remote_addr remote_addr VARCHAR(128) CHARACTER SET utf8,
change confirm_hash confirm_hash VARBINARY(128),
change surname surname VARCHAR(50) CHARACTER SET utf8,
change forename forename VARCHAR(50) CHARACTER SET utf8,
change title title VARBINARY(10),
change MultiSiteNavState MultiSiteNavState VARCHAR(255) CHARACTER SET utf8,
change status status VARCHAR(20) CHARACTER SET utf8;
-- contented_user_access_right
--
-- setting
alter table setting
change Type Type VARBINARY(20),
change DisplayName DisplayName VARBINARY(255),
change Name Name VARBINARY(50),
change Value Value VARBINARY(255),
change Status Status VARBINARY(20);
alter table setting
change Type Type VARCHAR(20) CHARACTER SET utf8,
change DisplayName DisplayName VARCHAR(255) CHARACTER SET utf8,
change Name Name VARCHAR(50) CHARACTER SET utf8,
change Value Value VARCHAR(255) CHARACTER SET utf8,
change Status Status VARCHAR(20) CHARACTER SET utf8;
-- sys_session
alter table sys_session
change SessionID SessionID VARBINARY(20),
change Message Message VARBINARY(255),
change RemoteAddress RemoteAddress VARBINARY(50),
change SessionData SessionData VARBINARY(255);
alter table sys_session
change SessionID SessionID VARCHAR(20) CHARACTER SET utf8,
change Message Message VARCHAR(255) CHARACTER SET utf8,
change RemoteAddress RemoteAddress VARCHAR(50) CHARACTER SET utf8,
change SessionData SessionData VARCHAR(20) CHARACTER SET utf8;
-- to top it all off, lets convert all tables to InnoDB:
-- user this query to generate ANOTHER query that you then run:
-- SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;')
-- FROM Information_schema.TABLES
-- WHERE TABLE_SCHEMA = 'contented1917' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE';
-- @TODO: #1214 - The used table type doesn't support FULLTEXT indexes
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.