how to insert an article into a wordpress database using mysql

there are a million reasons why you might want to add a new wordpress article manually using mysql. for me, i wanted to migrate custom-coded photo gallery content into the main wordpress installation, so that things on the website would become more streamlined and update-able.
most articles on the net push the idea of using wordpress’ own functions to add or alter blog posts in php (and probably for a reason). but if, like me, you just want a one-time set of mysql commands, here’s a “quick and dirty” way that has worked for me in wordpress 4.9.5:

the basics
example of a quick link to a blog post using the article ID (database: wp_posts.ID):
http://www.thinkoholic.com/?p=28714

quick link to edit a post using article ID:
http://www.thinkoholic.com/wordpress/wp-admin/post.php?action=edit&post=28714 (requires login, obviously)

the very least you’ll need to do in mysql, is create your blog post in wp_posts, and then assign a category in wp_term_relationships:

step 1: insert your article into table wp_posts using mysql

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_status`, `comment_status`, `ping_status`, `post_name`, `post_modified`, `post_modified_gmt`, `guid`, `post_type`, `comment_count`) VALUES ([ID], [AUTHOR], '[DATE1]', '[DATE2]', '[CONTENT]', '[TITLE]', 'publish', 'open', 'open', '[POSTNAME]', '[DATE3]', '[DATE4]', '[GUID]', 'post', 0);

details:

• [ID] needs to be a unique number that increases for each entry. note this number as you’ll need it for step 2.
• [AUTHOR] should be your author ID. you can find it in table wp_users.
• [DATE1] and [DATE2] will be your post date and time, first in your timezone and second in GMT, using the format “2005-12-31 01:23:45”. these timestamps will be used to show when the article was published.
• [CONTENT] is the main content of your blog article.
• [TITLE], unsurprisingly, is where your blog title goes.
• [POSTNAME] is typically a cleaned-up version of your title with only alphanumeric characters and hyphens (“−”). it can have no special characters or spaces (usually replaced with a hyphen), and it also shouldn’t have any double hyphens (“−−”).
• [DATE3] and [DATE4] are, as far as i can tell, just shown in the backend.
• and finally, [GUID] is an important one. it’s the complete URL (or permalink) for your article, including your domain name (e.g. “thinkoholic.com”), any folder structure (such as “/year/month/day”) and a unique article identifier. like [POSTNAME] it can’t have any special characters or double hyphens. in my script, i used a combination of my post’s name and the article ID (see example below), so i’d be sure it’s definitely unique across the entire wp_posts table. getting the GUID wrong will result in your article being unreachable, even if you link to it using just its ID (e.g. http://www.thinkoholic.com/?p=28714) – see “troubleshooting” below.

here’s an actual example from my own migration:
INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_status`, `comment_status`, `ping_status`, `post_name`, `post_modified`, `post_modified_gmt`, `guid`, `post_type`, `comment_count`) VALUES (28716, 1, '2018-05-30 00:00:00', '2018-05-30 00:00:00', 'gallery test post <br/><br/><br/><a href="http://www.thinkoholic.com/?p=98">related blog post</a>', 'photo gallery: fireworks', 'publish', 'open', 'open', 'photo-gallery-fireworks-s200-en', '2018-04-24 23:59:59', '2018-04-24 23:59:59', 'http://www.thinkoholic.com/2005/01/01/photo-gallery-fireworks-s200-en-28716', 'post', 0);

step 2: assign at least one category using mysql

now it’s time to assign a category, and possibly also tags, in wp_term_relationships. the post category is NOT defined in wp_posts itself (despite there being a field ‘post_category’), but in another table, wp_term_relationships.
you’ll need the correct ID number for your category or tag. to find which number to use as “term_taxonomy_id” in the second mysql statement, find the term ID of the desired category in wp_terms, then search for this term_id in wp_term_taxonomy and note the number in term_taxonomy_id. this is your category’s real unique identifier. note that term_id and term_taxonomy_id are probably similar but different!

for each category or tag you want to assign to the post, create a separate entry in wp_term_relationships. be sure to use the article ID specified in the above command as your object_id. here’s one example each for category and tag:

assign article 28716 to a category. in my case, category “photos” = term_taxonomy_id 5:
INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (28716, 5);

assign article 28716 to a tag. in my case, tag “old gallery” = term_taxonomy_id 222:
INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`) VALUES (28716, 222);

troubleshooting and code clippings

URL 404 problems:
if you’ve pushed an article into your database this way, and calling the post using http://thinkoholic.com?p=xxx results in a 404 error message, the problem is most likely in your “guid” field: it defines the article’s URI, and needs to be sanitized – only alphanumeric characters (a-z, 0-9) and hyphens (“-“), no special chars, no double hyphens (“–“) allowed either!
if this concerns just a few articles and you’re happy to do it manually, just open the article in wordpress and click “update”, and the “slug”/”guid” field is automatically checked and updated.

post-fixes:
mysql command to find posts with double hyphens in the GUID:
SELECT * FROM `wp_posts` WHERE `guid` LIKE '%--%';

this one’s more a very specific note-to-self than anything else: find posts with hardcoded gallery that have html-links in image alt-texts.
SELECT * FROM `wp_posts` WHERE `post_content` REGEXP 'alt=\\".[^\\"]*\\<a.*\\"' AND `post_type` = 'post' ORDER BY `ID` DESC;

in php, a better version to do find these would be:
/(alt|title)\=.*(<a href).*?(\<\/a>)/

please let me know in the comments whether this works for you and/or if you have a better database-side way of inserting posts into wordpress!