MySQL Interview Questions: Case Sensitivity, InnoDB Transaction Logs, and Binlog Formats

Introduction

Based on feedback from interview participants, three MySQL interview questions are compiled here. The questions cover case sensitivity in queries, InnoDB transaction and log mechanisms, and the various binlog recording formats.

1. Is MySQL query field case‑sensitive?

The default answer is **no**; MySQL uses a case‑insensitive collation (utf8_general_ci). To enforce case sensitivity you can switch to a case‑sensitive collation such as utf8_general_cs or utf8_bin , or use the BINARY keyword in queries.

Example of creating a table with a case‑sensitive collation:

-- 创建表：
CREATE TABLE testt(
  id INT PRIMARY KEY,
  name VARCHAR(32) NOT NULL
) ENGINE=INNODB COLLATE=utf8_bin;

-- 修改表结构的 Collation 属性
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

Another solution is to prepend BINARY to column references in the SELECT statement:

-- 在每一个条件前加上 binary 关键字
SELECT * FROM user WHERE binary username = 'admin' AND binary password = 'admin';

-- 将参数以 binary('') 包围
SELECT * FROM user WHERE username LIKE binary('admin') AND password LIKE binary('admin');

2. How does InnoDB implement transactions and logs?

MySQL maintains several log types: error log, general query log, slow‑query log, binary log, relay log, and transaction logs (redo and undo). InnoDB supports four isolation levels: Read Uncommitted (RU), Read Committed (RC), Repeatable Read (RR), and Serial.

Transaction logging works via the redo log and the InnoDB log buffer. When a transaction starts, a log sequence number (LSN) is assigned. All modifications are written to the log buffer; on commit, the buffer is flushed to disk according to the innodb_flush_log_at_trx_commit setting, implementing the “write‑ahead logging” (pre‑write log) mechanism.

3. What are the MySQL binlog formats and their differences?

There are three binlog formats:

Statement : records the original SQL statements. It reduces log size but requires additional context information for correct replication, and some functions (e.g., SLEEP() , LAST_INSERT_ID() , UDFs) cannot be replicated.

Row : records the actual row changes. It captures precise data modifications and avoids many replication issues, but can generate large logs for bulk updates or DDL operations.

Mixed : combines both; MySQL chooses Statement or Row based on the statement type.

Statement format is efficient for simple DML, while Row format is necessary for statements that cannot be safely replicated in Statement mode. Newer MySQL versions optimize Row logging for DDL changes, but still log each row for UPDATE/DELETE operations.

Additional Links

The article concludes with promotional links to other interview‑oriented topics such as message queues, I/O models, and design patterns.