校园项目爬虫存入MySQL遇1366错误：字符串值不正确问题咨询

Hey, let's fix that InternalError: 1366 you're hitting! The root issue here is a super common MySQL gotcha: the utf8 character set you're using for your content and content_raw columns is actually utf8mb3 under the hood—it only supports Unicode characters up to 3 bytes. The messy string value in your error (\xC4\x82\xC5\xA4 \xC3...) tells me you're trying to insert 4-byte characters (like emojis, rare accented symbols, or certain special characters) that this limited charset can't handle.

Quick Fix: Update Your Table to Use utf8mb4 #

First, you need to switch those columns to utf8mb4—the full UTF-8 character set that supports every Unicode character out there. Run this SQL query:

ALTER TABLE scotchbox.content 
MODIFY COLUMN content LONGTEXT CHARACTER SET utf8mb4 NOT NULL,
MODIFY COLUMN content_raw LONGTEXT CHARACTER SET utf8mb4 NOT NULL;

If you want to set the entire table to use utf8mb4 by default (a smart move for future columns), you can run this too:

ALTER TABLE scotchbox.content CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Don't Skip the Connection Encoding! #

Updating the table alone won't cut it—you need to make sure your crawler's database connection uses utf8mb4 encoding too. Here's how to do it in common languages:

• Python (with pymysql): Add the charset parameter to your connection setup:

  import pymysql
  conn = pymysql.connect(
      host="your_db_host",
      user="your_db_user",
      password="your_db_pass",
      db="scotchbox",
      charset="utf8mb4"
  )
  

• PHP (with mysqli): Set the charset right after connecting:

  $conn = mysqli_connect("your_db_host", "your_db_user", "your_db_pass", "scotchbox");
  mysqli_set_charset($conn, "utf8mb4");
  

Bonus: Ensure Scraped Content is Clean UTF-8 #

Before inserting, double-check that the content you're crawling is properly encoded as UTF-8. In Python, you can normalize messy encoding with this quick line:

# Fixes invalid UTF-8 sequences instead of letting them break inserts
clean_content = raw_scraped_content.encode('utf-8', errors='replace').decode('utf-8')

内容的提问来源于stack exchange，提问作者Rick van der Heide