跳至正文
OpenAgentSkill
浏览已开放 · 目录记录日期 2026-11-21 · 自动更新和账户服务暂时关闭。
数据与分析

wp-database

Use when a WordPress plugin needs a custom database table — creating with dbDelta (strict SQL format: two spaces before PRIMARY KEY, no trailing comma), writing versioned upgrade routines with version_compare and register_activation_hook, querying with $wpdb prepared statements (prepare, insert, update, delete, get_row, get_results, get_var), debugging with $wpdb->last_error or SAVEQUERIES, schema migrations between plugin versions, uninstall cleanup, multisite table handling with switch_to_blog / get_sites, or seeding test/preview data into custom tables. Triggers: \"create a custom table\", \"dbDelta not working\", \"dbDelta not creating my table\", \"write a migration\", \"wpdb query\", \"wpdb prepare\", \"slow query on my custom table\", \"upgrade my database schema\", \"add a column to my table\", \"seed test data\", \"how do I store this in a custom table\", \"database upgrade routine\", \"table not being created on activation\", \"prepare placeholder wrong\", \"last_error is emp

mralaminahamed · 已有静态检查记录

安装与使用

运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。

npx skills add mralaminahamed/wp-dev-skills --skill wp-database

技能说明


name: wp-database description: "Use when a WordPress plugin needs a custom database table — creating with dbDelta (strict SQL format: two spaces before PRIMARY KEY, no trailing comma), writing versioned upgrade routines with version_compare and register_activation_hook, querying with $wpdb prepared statements (prepare, insert, update, delete, get_row, get_results, get_var), debugging with $wpdb->last_error or SAVEQUERIES, schema migrations between plugin versions, uninstall cleanup, multisite table handling with switch_to_blog / get_sites, or seeding test/preview data into custom tables. Triggers: "create a custom table", "dbDelta not working", "dbDelta not creating my table", "write a migration", "wpdb query", "wpdb prepare", "slow query on my custom table", "upgrade my database schema", "add a column to my table", "seed test data", "how do I store this in a custom table", "database upgrade routine", "table not being created on activation", "prepare placeholder wrong", "last_error is empty after query", "SAVEQUERIES debug", "version_compare for db upgrade", "register_activation_hook table", "wpdb prefix table name", "uninstall drops table", "multisite per-site table", "charset_collate missing". Not for: WordPress options API or post meta — use those when a custom table is not needed."

WordPress Custom Database Tables

Model note: dbDelta schema and CRUD patterns are mechanical (haiku). Query optimisation and multi-version data migrations require cross-file reasoning — use sonnet for those sub-tasks.

Create and manage custom database tables in WordPress plugins: dbDelta() for schema definition, versioned upgrade routines, $wpdb CRUD with prepared statements, and data migration strategies.

When to use

  • "Create a custom DB table for my plugin", "set up plugin schema with dbDelta".
  • "Write a migration for plugin upgrade", "run schema changes on update".
  • "Query a custom table", "insert/update/delete with $wpdb".
  • "Optimise a slow custom query", "add an index to a plugin table".
  • "Migrate data from post meta to a custom table".

Not for: WooCommerce order table operations — use wp-woocommerce. General $wpdb query optimisation in core WP tables — use wp-performance (official skill).

Method

1. Create table with dbDelta

dbDelta() is the only WP-safe way to create and alter tables — it diffs the current schema against the SQL and applies only the necessary changes.

function my_plugin_create_tables() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate(); // e.g. DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci

    // $wpdb->prefix respects multisite site prefix automatically
    $table_log  = $wpdb->prefix . 'my_plugin_log';
    $table_meta = $wpdb->prefix . 'my_plugin_item_meta';

    // IMPORTANT: two spaces before PRIMARY KEY, one space before each KEY
    // IMPORTANT: no trailing comma on last field before closing paren
    $sql = "CREATE TABLE {$table_log} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  item_id bigint(20) unsigned NOT NULL,
  action varchar(100) NOT NULL DEFAULT '',
  message longtext NOT NULL,
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY item_id (item_id),
  KEY created_at (created_at)
) {$charset_collate};

CREATE TABLE {$table_meta} (
  meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  item_id bigint(20) unsigned NOT NULL,
  meta_key varchar(255) NOT NULL DEFAULT '',
  meta_value longtext,
  PRIMARY KEY  (meta_id),
  KEY item_id (item_id),
  KEY meta_key (meta_key(191))
) {$charset_collate};";

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

Critical dbDelta formatting rules (violations cause silent failures):

  • Two spaces after PRIMARY KEY (e.g. PRIMARY KEY (id))
  • Field definitions must end with a comma except the last field before the closing paren
  • KEY lines go after all field definitions, before the closing paren
  • Only CREATE TABLE statements — no ALTER TABLE (dbDelta handles column additions, not removals)
  • Always include {$charset_collate} at the end

2. Versioned upgrade routine

Track schema version in an option; only re-run dbDelta when the version changes:

define( 'MY_PLUGIN_DB_VERSION', '1.3.0' );

function my_plugin_maybe_upgrade_db() {
    $installed = get_option( 'my_plugin_db_version', '0' );
    if ( version_compare( $installed, MY_PLUGIN_DB_VERSION, '>=' ) ) {
        return; // already up to date
    }

    my_plugin_create_tables(); // always safe to re-run dbDelta

    // Version-specific data migrations
    if ( version_compare( $installed, '1.2.0', '<' ) ) {
        my_plugin_migrate_to_1_2_0();
    }
    if ( version_compare( $installed, '1.3.0', '<' ) ) {
        my_plugin_migrate_to_1_3_0();
    }

    update_option( 'my_plugin_db_version', MY_PLUGIN_DB_VERSION );
}
add_action( 'plugins_loaded', 'my_plugin_maybe_upgrade_db' );

Run on plugins_loaded (every request until updated), not just on activation — catches updates after auto-update or version switch.

3. $wpdb CRUD

Always use $wpdb->prepare() for any value from user input or untrusted source.

Insert:

$result = $wpdb->insert(
    $wpdb->prefix . 'my_plugin_log',
    [
        'item_id'    => $item_id,
        'action'     => 'view',
        'message'    => $message,
        'user_id'    => get_current_user_id(),
        'created_at' => current_time( 'mysql' ),
    ],
    [ '%d', '%s', '%s', '%d', '%s' ] // format for each value: %d int, %s string, %f float
);
$inserted_id = $wpdb->insert_id;

Update:

$wpdb->update(
    $wpdb->prefix . 'my_plugin_log',
    [ 'message' => $new_message ],          // data
    [ 'id'      => $log_id ],               // where
    [ '%s' ],                               // data format
    [ '%d' ]                   
阅读完整来源 ↗