Defining MySQL Engine and Character Set when creating a MySQL Table

MySQL may not always be configured with defaults. The major issues we've noticed with BeBot development is using features that are only supported in a specific storage engine or users who's MySQL database is configured to use a character set such as UTF8 that uses more bits per character. The default character set and default storage engine are changeable in the MySQL configuration file, however users may not have access to modify their MySQL configuration. These options can be set when creating the table, so when a specific character set or MySQL Storage Engine is required by BeBot it should be specified when the table is created.

Setting Character Set

For some tables (such as security and settings) we want to force using the latin1 character set. To do so, append DEFAULT CHARSET=latin1 to your CREATE TABLE command.

CREATE TABLE IF NOT EXISTS `settings` ( `module` varchar(255) NOT NULL default , `setting` varchar(255) NOT NULL default , `value` varchar(255) default NULL, `datatype` varchar(255) default NULL, PRIMARY KEY (`module`,`setting`) ) DEFAULT CHARSET=latin1;

Setting Storage Engine

The quotes module depends on MySQL's full text index feature that is only available in with the MyISAM storage engine. MyISAM is the default MySQL storage engine, but the Windows installer for MySQL sets the default storage engine to InnoDB. To ensure that your tables are created using the proper storage engine, append ENGINE=MyISAM to your CREATE TABLE command.

CREATE TABLE IF NOT EXISTS quotes (quote varchar(255), contributor varchar(255)) ENGINE=MyISAM;

Setting Default Character Set and Storage Engine

To set both the default character set and engine:

CREATE TABLE IF NOT EXISTS quotes (quote varchar(255), contributor carchar(255)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 
mysql_engine_and_charset.txt · Last modified: 2013/09/12 22:49 (external edit)
[unknown button type]
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 3.0 Unported
WikiForumIRCBugs
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki