Hey there, let's break down why your CREATE TABLE statement is throwing errors and fix it step by step!

First, let's spot the obvious syntax mistakes in your original SQL: #

• Wrong table definition syntax: After CREATE TABLE $table_name, you used a comma , instead of opening parentheses ( to wrap your column definitions. Databases expect CREATE TABLE tablename (columns...), so that comma is a critical syntax error that breaks the entire query.
• Incomplete UNIQUE KEY definition: UNIQUE KEY id is missing the parentheses that specify which column the key applies to. It should be UNIQUE KEY id (id) — though since id is an auto-increment column, using PRIMARY KEY (id) is more standard (primary keys are inherently unique and optimized for database lookups).

Next, let's fix compatibility and best practice issues: #

• bit type isn't ideal for WordPress: While MySQL supports bit, the WordPress ecosystem almost universally uses tinyint(1) to represent boolean values (0 = false, 1 = true). This avoids compatibility quirks across different hosting environments and aligns with how WordPress stores similar values internally.
• Overly large integer types: int(12) for start_hour and end_hour is overkill — since hours only range from 0-23, tinyint(2) is more than enough and saves unnecessary storage space.
• Missing explicit default values: For columns like userid, accepted, and deleted, adding explicit DEFAULT NULL or DEFAULT 0 makes your table schema clearer and avoids relying on database-specific default behaviors.
• Formalizing charset/collation: You mentioned applying default collation, so we'll use WordPress's built-in $wpdb->get_charset_collate() to ensure we match the site's default encoding (usually utf8mb4_unicode_ci, which supports all Unicode characters including emojis).

Here's the corrected, WordPress-friendly code: #

global $wpdb;
// Use the WordPress table prefix to avoid conflicts with other plugins/themes
$table_name = $wpdb->prefix . 'your_custom_table'; 
$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    userid bigint(20) DEFAULT NULL,
    start_hour tinyint(2) NOT NULL,
    end_hour tinyint(2) NOT NULL,
    accepted tinyint(1) DEFAULT 0,
    deleted tinyint(1) DEFAULT 0,
    PRIMARY KEY (id)
) $charset_collate;";

// Load WordPress's upgrade functions and execute the query safely
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

A quick note on using dbDelta(): #

This is WordPress's recommended way to create or update tables. Unlike a raw $wpdb->query() call, dbDelta() will check if the table already exists and only update the schema if needed — it's safer for plugin updates and avoids accidental table drops or duplicate creation.

If you still run into issues, enable WP_DEBUG in your wp-config.php file (set define('WP_DEBUG', true);) — this will show you the exact MySQL error message, which can help pinpoint any remaining edge cases.

内容的提问来源于stack exchange，提问作者Tails128