MySQL插入@字符问题：latin_spanish_ci排序规则下的实现方法

Alright, let's break down how to get that @ character inserted correctly into your table. First, let's look at your current character set configuration: your client/connection uses utf8, but your database and server are set to latin1, and your table uses latin_spanish_ci (which is a collation tied to the latin1 character set).

Here are a few reliable ways to make the INSERT work smoothly:

1. 临时调整会话字符集（推荐） #

Since your table's character set is latin1 (matching its latin_spanish_ci collation), you can temporarily set your session's character set to match before running the INSERT. This avoids any unexpected encoding conversions:

-- First set the session character set to latin1
SET NAMES latin1;

-- Now run your INSERT statement
INSERT INTO `tbl002_club` (`id`, `name`, `web`, `twitter`) VALUES (1, 'Club Name', 'example@domain.com', '@ClubHandle');

SET NAMES latin1 updates three key variables at once: character_set_client, character_set_connection, and character_set_results to latin1, ensuring consistency between your client input and the table's encoding.

2. 显式转换字符串编码 #

If you don't want to change the session character set, you can use MySQL's CONVERT() function to explicitly convert the strings containing @ to latin1 during insertion:

INSERT INTO `tbl002_club` (`id`, `name`, `web`, `twitter`) 
VALUES (
    1, 
    'Club Name', 
    CONVERT('example@domain.com' USING latin1), 
    CONVERT('@ClubHandle' USING latin1)
);

This forces the specific strings to be encoded in latin1 before they're written to the table, matching its character set.

3. 确认表的字符集（排查步骤） #

Just to be thorough, double-check that your table is indeed using latin1 as its character set (since latin_spanish_ci is a latin1 collation). Run this query to verify:

SHOW CREATE TABLE tbl002_club;

Look for the CHARACTER SET latin1 and COLLATE latin_spanish_ci in the output—if those are present, you're good to go with the above solutions.

One quick note: The @ character is part of the ASCII set, so it's represented the same way in both utf8 and latin1 (single byte, hex 40). But ensuring your connection encoding matches the table's avoids any edge cases with other characters you might insert later.

内容的提问来源于stack exchange，提问作者José Carlos