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:
dbDeltaschema and CRUD patterns are mechanical (haiku). Query optimisation and multi-version data migrations require cross-file reasoning — usesonnetfor 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
KEYlines go after all field definitions, before the closing paren- Only
CREATE TABLEstatements — noALTER 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' ]
阅读完整来源 ↗