Default table collation on plugin activation?

Question

I'm following this to make my plugin auto-create a table when the plugin is activated, but what happens is that while all the tables (the whole db) are utf8_general_ci, the newly created table is latin1_swedish_ci... Why isn't it also utf8? I thought it would also be utf8 by default since I have:

define('DB_COLLATE', 'utf8_general_ci');

in my wp-config.php... I have everything exactly the same like in the link provided, except the function name, and I have different SQL fields... How to make the newly created table utf8 by default?

this is my function:

function duende_install() {
global $wpdb;
global $duende_db_version;

$table_name = $wpdb->prefix . "duendes";
if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
    $sql = "CREATE TABLE " . $table_name . " (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        shape tinytext NOT NULL,
        integrity tinytext NOT NULL,
        length tinytext NOT NULL,
        drone tinytext NOT NULL,
        wood tinytext NOT NULL,
        mouth tinytext NOT NULL,            
        rim tinytext NOT NULL,
        bell tinytext NOT NULL,                     
        loud tinytext NOT NULL,                     
        mass tinytext NOT NULL,                     
        finish tinytext NOT NULL,   
        inlay tinytext NOT NULL,                    
        price smallint DEFAULT '0' NOT NULL,
        sold tinytext NOT NULL,                                             
        UNIQUE KEY id (id)
    );";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    add_option("duende_db_version", $duende_db_version);
}
}

Thank you

Answer 1

You're missing the point that there is collation not only for the DB but as well for tables and even fields.

Therefore from your point of view, I assume that your CREATE TABLE statement is "not complete". Before using SQL statements, please learn the language first.

See CREATE TABLE Syntax (MySQL Manual).

Especially table_options / table_option and [DEFAULT] COLLATE [=] collation_name there in.

UPDATE:

Please see Wordpress Database Charset and Collation Configuration for an in-depth description where and how to setup wordpress regarding charset and collation.

Answer 2

just in case somebody does need this, you could set the collation this way.. the row after the UNIQUE KEY id (id) part should look like this:

) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";

Answer 3

The $wpdb object now has a member function for retrieving the character set and collation as a string that can be added directly to SQL CREATE TABLE queries.

$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name ( $fields ) $charset_collate;"

This string will line up with the WP_CHARSET and WP_COLLATE values from the configuration file (if present), and is used internally to create Wordpress's default tables.

Answer 4

I've been using this

  global $wpdb;
  // $charset_collate = $wpdb->get_charset_collate(); //This outputs an unsupported charset for a lot of non english speakers for some reason
  $charset_collate = "";
  $collation = $wpdb->get_row("SHOW FULL COLUMNS FROM {$wpdb->posts} WHERE field = 'post_content'");

  if(isset($collation->Collation)) {
    $charset = explode('_', $collation->Collation);

    if(is_array($charset) && count($charset) > 1) {
      $charset = $charset[0]; //Get the charset from the collation
      $charset_collate = "DEFAULT CHARACTER SET {$charset} COLLATE {$collation->Collation}";
    }
  }

  if(empty($charset_collate)) { $charset_collate = $wpdb->get_charset_collate(); } //Okay fine, we'll try it your way