WordPress使用TiDB数据库

因为工作原因接触到TiDB，根据介绍与Mysql高度兼容，于是便有了WordPress使用TiDB数据库这一文章。
搜索到的一篇文章是直接可以搭建，我却不能直接顺利就这样安装成功，要小改一下。
或许是版本的区别吧。
我使用的版本如下：

TiDB的版本是：5.7.25-TiDB-v6.0.0；

WordPress的版本是：6.0。

错误也很简单，就是不支持WordPress需要的数据库排序规则 。
错误提示：WordPress数据库错误： [Unsupported collation when new collation is enabled: 'utf8mb4_unicode_520_ci']
百般搜索都不知道怎么开启这个utf8mb4_unicode_520_ci数据库排序规则，最后没办法只好将数据库排序规则改成utf8mb4_unicode_ci，然后时间类型DATETIME的默认值0000-00-00 00:00:00也不行于是就改成CURRENT_TIMESTAMP。

于是WordPress的数据库表结构就这样创建好了，可是创建了表结构WordPress就不会在初始化数据库，没有初始化就无法有默认数据导致没有默认数据。。。。。。

好在搜索到一个初始化的“脚本”。

数据库表结构SQL语句：

CREATE TABLE wp_users (
	ID BIGNT(20) UNSIGNED NOT NULL auto_increment,
	user_login VARCHAR(60) NOT NULL DEFAULT '',
	user_pass VARCHAR(255) NOT NULL DEFAULT '',
	user_nicename VARCHAR(50) NOT NULL DEFAULT '',
	user_email VARCHAR(100) NOT NULL DEFAULT '',
	user_url VARCHAR(100) NOT NULL DEFAULT '',
	user_registered DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP,
	user_activation_key VARCHAR(255) NOT NULL DEFAULT '',
	user_status INT(11) NOT NULL DEFAULT '0',
	display_name VARCHAR(250) NOT NULL DEFAULT '',
	PRIMARY KEY (ID),
	KEY user_login_key (user_login),
	KEY user_nicename (user_nicename),
	KEY user_email (user_email) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_usermeta (
	umeta_id BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	user_id BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	meta_key VARCHAR ( 255 ) DEFAULT NULL,
	meta_value LONGTEXT,
	PRIMARY KEY ( umeta_id ),
	KEY user_id ( user_id ),
	KEY meta_key (
	meta_key ( 191 )) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_termmeta (
	meta_id BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	term_id BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	meta_key VARCHAR ( 255 ) DEFAULT NULL,
	meta_value LONGTEXT,
	PRIMARY KEY ( meta_id ),
	KEY term_id ( term_id ),
	KEY meta_key (
	meta_key ( 191 )) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_terms (
	term_id BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	NAME VARCHAR ( 200 ) NOT NULL DEFAULT '',
	slug VARCHAR ( 200 ) NOT NULL DEFAULT '',
	term_group BIGINT ( 10 ) NOT NULL DEFAULT 0,
	PRIMARY KEY ( term_id ),
	KEY slug (
	slug ( 191 )),
	KEY NAME (
	NAME ( 191 )) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_term_taxonomy (
	term_taxonomy_id BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	term_id BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT 0,
	taxonomy VARCHAR ( 32 ) NOT NULL DEFAULT '',
	description LONGTEXT NOT NULL,
	parent BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT 0,
	count BIGINT ( 20 ) NOT NULL DEFAULT 0,
	PRIMARY KEY ( term_taxonomy_id ),
	UNIQUE KEY term_id_taxonomy ( term_id, taxonomy ),
	KEY taxonomy ( taxonomy ) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_term_relationships (
	object_id BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT 0,
	term_taxonomy_id BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT 0,
	term_order INT ( 11 ) NOT NULL DEFAULT 0,
	PRIMARY KEY ( object_id, term_taxonomy_id ),
	KEY term_taxonomy_id ( term_taxonomy_id ) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_commentmeta (
	meta_id BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	comment_id BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	meta_key VARCHAR ( 255 ) DEFAULT NULL,
	meta_value LONGTEXT,
	PRIMARY KEY ( meta_id ),
	KEY comment_id ( comment_id ),
	KEY meta_key (
	meta_key ( 191 )) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_comments (
	comment_ID BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	comment_post_ID BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	comment_author TINYTEXT NOT NULL,
	comment_author_email VARCHAR ( 100 ) NOT NULL DEFAULT '',
	comment_author_url VARCHAR ( 200 ) NOT NULL DEFAULT '',
	comment_author_IP VARCHAR ( 100 ) NOT NULL DEFAULT '',
	comment_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	comment_date_gmt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	comment_content text NOT NULL,
	comment_karma INT ( 11 ) NOT NULL DEFAULT '0',
	comment_approved VARCHAR ( 20 ) NOT NULL DEFAULT '1',
	comment_agent VARCHAR ( 255 ) NOT NULL DEFAULT '',
	comment_type VARCHAR ( 20 ) NOT NULL DEFAULT 'comment',
	comment_parent BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	user_id BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY ( comment_ID ),
	KEY comment_post_ID ( comment_post_ID ),
	KEY comment_approved_date_gmt ( comment_approved, comment_date_gmt ),
	KEY comment_date_gmt ( comment_date_gmt ),
	KEY comment_parent ( comment_parent ),
	KEY comment_author_email (
	comment_author_email ( 10 )) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_links (
	link_id BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	link_url VARCHAR ( 255 ) NOT NULL DEFAULT '',
	link_name VARCHAR ( 255 ) NOT NULL DEFAULT '',
	link_image VARCHAR ( 255 ) NOT NULL DEFAULT '',
	link_target VARCHAR ( 25 ) NOT NULL DEFAULT '',
	link_description VARCHAR ( 255 ) NOT NULL DEFAULT '',
	link_visible VARCHAR ( 20 ) NOT NULL DEFAULT 'Y',
	link_owner BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '1',
	link_rating INT ( 11 ) NOT NULL DEFAULT '0',
	link_updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	link_rel VARCHAR ( 255 ) NOT NULL DEFAULT '',
	link_notes MEDIUMTEXT NOT NULL,
	link_rss VARCHAR ( 255 ) NOT NULL DEFAULT '',
	PRIMARY KEY ( link_id ),
	KEY link_visible ( link_visible ) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_options (
	option_id BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	option_name VARCHAR ( 191 ) NOT NULL DEFAULT '',
	option_value LONGTEXT NOT NULL,
	autoload VARCHAR ( 20 ) NOT NULL DEFAULT 'yes',
	PRIMARY KEY ( option_id ),
	UNIQUE KEY option_name ( option_name ),
	KEY autoload ( autoload ) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_postmeta (
	meta_id BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	post_id BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	meta_key VARCHAR ( 255 ) DEFAULT NULL,
	meta_value LONGTEXT,
	PRIMARY KEY ( meta_id ),
	KEY post_id ( post_id ),
	KEY meta_key (
	meta_key ( 191 )) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

CREATE TABLE wp_posts (
	ID BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment,
	post_author BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	post_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	post_date_gmt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	post_content LONGTEXT NOT NULL,
	post_title text NOT NULL,
	post_excerpt text NOT NULL,
	post_status VARCHAR ( 20 ) NOT NULL DEFAULT 'publish',
	comment_status VARCHAR ( 20 ) NOT NULL DEFAULT 'open',
	ping_status VARCHAR ( 20 ) NOT NULL DEFAULT 'open',
	post_password VARCHAR ( 255 ) NOT NULL DEFAULT '',
	post_name VARCHAR ( 200 ) NOT NULL DEFAULT '',
	to_ping text NOT NULL,
	pinged text NOT NULL,
	post_modified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	post_modified_gmt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	post_content_filtered LONGTEXT NOT NULL,
	post_parent BIGINT ( 20 ) UNSIGNED NOT NULL DEFAULT '0',
	guid VARCHAR ( 255 ) NOT NULL DEFAULT '',
	menu_order INT ( 11 ) NOT NULL DEFAULT '0',
	post_type VARCHAR ( 20 ) NOT NULL DEFAULT 'post',
	post_mime_type VARCHAR ( 100 ) NOT NULL DEFAULT '',
	comment_count BIGINT ( 20 ) NOT NULL DEFAULT '0',
	PRIMARY KEY ( ID ),
	KEY post_name (
	post_name ( 191 )),
	KEY type_status_date ( post_type, post_status, post_date, ID ),
	KEY post_parent ( post_parent ),
	KEY post_author ( post_author ) 
) DEFAULT CHARACTER 
SET utf8mb4 COLLATE utf8mb4_unicode_ci

创建好表结构后，配置好WordPress的连接信息后在wp-config.php同级目录新建一个php文件，php内容如下：

<?php
define( 'WP_INSTALLING', true );

/** Load WordPress Bootstrap */
require_once( dirname( __FILE__ ) . '/wp-load.php' );

/** Load WordPress Administration Upgrade API */
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

/** Load WordPress Translation Install API */
require_once( ABSPATH . 'wp-admin/includes/translation-install.php' );

/** Load wpdb */
require_once( ABSPATH . WPINC . '/wp-db.php' );

define('WP_SITEURL', 'http://wordpress');
$weblog_title = 'TiDB';
$user_name = 'admin';
$admin_email = '[email protected]';
$public = 1;
$admin_password = 'admin'; // in plain text
$loaded_language = '';
$result = wp_install($weblog_title, $user_name, $admin_email, $public, '', wp_slash( $admin_password ), $loaded_language);
print_r($result);
?>

于是就这样成功了，登录后台后查看文章可以显示有1篇文章却找不到列表数据，但是却能看到该篇文章。通过查看Apache日志，发现了错误信息 ... has only noop implementation in tidb now, use tidb_enable_noop_functions to enable these ... 最后找到了解决方法，忽略部分函数。

set global tidb_enable_noop_functions=1;

于是好险就这样可以跑起来了。。。。。。

参考：

WordPress 自动初始化数据库
https://blog.huati365.com/dd69491a6f268554

tidb替换mysql遇到的问题记录
https://blog.csdn.net/liusf1993/article/details/120116053

ChiuYut

2022年06月01日