Common MySQL Pitfalls and How to Avoid Them

1. Introduction

For anyone involved in Internet development, MySQL is a daily companion for DBAs, developers, and testers. This article shares several practical pitfalls that many may not have encountered.

2. Signed vs. Unsigned

When a column is guaranteed to store only positive numbers, defining it as UNSIGNED can halve the storage space.

create table test_unsigned(a int UNSIGNED, b int UNSIGNED);

Some prefer always using signed integers because MySQL defaults to signed and it is simpler to use.

create table test_signed(a int);</code><code>insert into test_signed values(-1);

Inserting -1 into a signed column succeeds, but using unsigned columns for subtraction can cause errors when the result is negative.

select b - a from test_unsigned;  -- returns 1

select a - b from test_unsigned;  -- error

Therefore, the team chose signed int fields to avoid such issues.

3. Auto‑Increment

Auto‑increment columns must be defined as a key.

create table test_auto_increment (a int auto_increment primary key);

If the PRIMARY KEY clause is omitted, MySQL reports:

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Experiments show that inserting NULL values generates sequential IDs, while explicit values (including negative numbers) are accepted, but inserting 0 succeeds without adding a row.

4. Column Length

For VARCHAR, length() returns byte count while CHARACTER_LENGTH() returns character count. In UTF‑8, a Chinese character occupies three bytes, so VARCHAR stores character length, not byte length.

create table test_varchar(a varchar(20));</code><code>insert into test_varchar values('苏三说技术');</code><code>select length(a), CHARACTER_LENGTH(a) from test_varchar;

Other numeric types represent byte length. The BIGINT(4) ZEROFILL example shows that the number in parentheses affects display width, not actual storage.

create table test_bigint (a bigint(4) ZEROFILL);</code><code>insert into test_bigint values(1), (123456);</code><code>select * from test_bigint;

5. Case Sensitivity

MySQL’s default collation utf8_general_ci is case‑insensitive, so a query for 'a' also returns rows with 'A'. To enforce case sensitivity, use a binary collation such as utf8_bin.

ALTER TABLE test_a MODIFY COLUMN a VARCHAR(20) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

After the change, a query for 'a' returns only the lowercase row.

6. Special Characters (Emoji)

Storing emoji with the default utf8 charset fails because MySQL’s utf8 supports at most three bytes per character, while an emoji requires four bytes. The solution is to switch to utf8mb4, which supports four‑byte characters.

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

After restarting MySQL, verify the settings with:

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

Individual databases, tables, or columns can also be converted to utf8mb4 using ALTER DATABASE, ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4, or

ALTER TABLE ... CHANGE ... VARCHAR(...) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

.

Adopting utf8mb4 from the start prevents many encoding‑related issues.