Lesson 5 of 5

Common Gotchas

The last stretch isn't new syntax to memorize — it's a handful of behavioral differences that don't show up until something works fine in development and then breaks (or, worse, silently returns wrong data) somewhere else: a different OS, a different table engine, a stricter server mode. These are the ones that cost the most debugging time precisely because nothing throws an error.

💡 The Bottom Line

SQL Server table and column names are case-insensitive by default, everywhere. MySQL's case-sensitivity for table names depends on the underlying filesystem (case-sensitive on Linux, case-insensitive on Windows and macOS by default) — the exact same script can behave differently depending only on what OS the MySQL server happens to run on. On top of that, MySQL's default storage engine (InnoDB) supports transactions much like SQL Server does, but older tables or a misconfigured engine choice (MyISAM) silently drop transactional guarantees, and MySQL has historically been more lenient than SQL Server about non-aggregated columns in a GROUP BY query. Treat all of these as "runs fine until it doesn't."

Case sensitivity of table names

SQL Server (T-SQL)

SELECT * FROM Orders;
SELECT * FROM orders;
SELECT * FROM ORDERS;
-- all three work identically

MySQL (on Linux)

SELECT * FROM Orders;   -- works
SELECT * FROM orders;   -- works only if the
                        -- table was created
                        -- as "orders"
SELECT * FROM ORDERS;   -- Error 1146:
                        -- table doesn't exist

📋 Key differences

SQL Server's default collation makes table, column, and database names case-insensitive for matching purposes, regardless of platform.
MySQL delegates table-name case sensitivity to the filesystem it's running on: Linux filesystems are typically case-sensitive, so MySQL table names are case-sensitive there; Windows and (by default) macOS filesystems are case-insensitive, so the same MySQL install behaves more like SQL Server on those platforms.
Column names, unlike table names, are always case-insensitive in MySQL, regardless of OS — the inconsistency is specifically about tables (and databases), not columns.

⚠️ Gotcha

A team develops on macOS or Windows, where Orders and orders are the same table, then deploys to a Linux production server, where they're two different (and one nonexistent) tables. The failure only appears in production, on whichever queries happen to use the "wrong" casing — and it's not a MySQL bug, it's the OS filesystem underneath it. The fix is discipline, not configuration: pick one casing convention (snake_case, all lowercase, is the overwhelming MySQL norm) and never deviate, so the filesystem's behavior never matters.

Transactions and storage engines

SQL Server's storage layer supports transactions as a fundamental, non-optional guarantee — you don't choose whether a table participates in transactions. MySQL's transactional support is a property of the storage engine a table uses, and MySQL supports more than one. The default engine on modern MySQL is InnoDB, which does support full ACID transactions, row-level locking, and foreign keys — behaviorally close to what a T-SQL developer already expects. The older MyISAM engine does not support transactions at all: a ROLLBACK on a MyISAM table simply does nothing, silently.

📋 Key differences

Check a table's engine with SHOW TABLE STATUS LIKE 'orders'; or SHOW CREATE TABLE orders; — the ENGINE= clause tells you immediately.
InnoDB is the default for new tables on any current MySQL version, so a fresh install behaves transactionally out of the box — the risk is almost entirely with older databases or an explicit ENGINE=MyISAM left over from a much older schema.
Foreign key constraints are also engine-dependent: MyISAM silently ignores FOREIGN KEY clauses in a CREATE TABLE statement (they parse but enforce nothing) — another reason a stray MyISAM table can look correct in the schema while enforcing none of its declared constraints.

⚠️ Gotcha

Wrapping statements in START TRANSACTION ... COMMIT/ROLLBACK against a MyISAM table doesn't raise an error — it just doesn't do anything. A ROLLBACK after a failed operation leaves every already-executed statement's changes in place, exactly as if no transaction had been used at all. Always confirm ENGINE=InnoDB on any table you expect transactional behavior from, especially on databases migrated from an older MySQL version.

GROUP BY strictness

SQL Server (T-SQL)

SELECT CustomerId, OrderDate, COUNT(*)
FROM Orders
GROUP BY CustomerId;
-- Error: OrderDate is invalid in the
-- select list because it's not
-- contained in an aggregate function
-- or the GROUP BY clause

MySQL (older / lenient mode)

SELECT customer_id, order_date, COUNT(*)
FROM orders
GROUP BY customer_id;
-- historically: runs, picks an
-- arbitrary order_date value per group

SQL Server has always enforced the ANSI rule strictly: every non-aggregated column in the select list must appear in the GROUP BY clause. Older MySQL versions were famously lenient — they'd run the query anyway and pick an arbitrary (essentially unpredictable) row's value for the non-grouped column. Modern MySQL (5.7.5+) enables ONLY_FULL_GROUP_BY in the default SQL mode, which brings its behavior in line with SQL Server's stricter rule — but the setting can be turned off, and plenty of production servers (or their configuration inherited from an older version) still run without it.

⚠️ Gotcha

Never rely on ONLY_FULL_GROUP_BY being on just because it's the modern default — check the server's actual sql_mode with SELECT @@sql_mode; before assuming a loose GROUP BY will be caught. A query that "worked" against a lenient server can start throwing errors the moment it's pointed at a strictly-configured one, or vice versa — silently return different arbitrary values per run against a lenient one. Either way, write GROUP BY queries as if the strict rule always applies: include every non-aggregated select-list column in the GROUP BY clause, on both engines.

Date/time function names

SQL Server (T-SQL)

SELECT GETDATE();
SELECT DATEADD(DAY, 7, GETDATE());
SELECT DATEDIFF(DAY, StartDate, EndDate);

MySQL

SELECT NOW();
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATEDIFF(end_date, start_date);

📋 Key differences

GETDATE() becomes NOW() (MySQL also has CURDATE() and CURTIME() for just the date or time portion).
DATEADD(unit, number, date) becomes DATE_ADD(date, INTERVAL number unit) — note the argument order flips, and the unit/number pairing becomes a single INTERVAL expression.
MySQL's DATEDIFF(a, b) always returns whole days (a - b) and takes no unit argument; SQL Server's DATEDIFF(unit, a, b) takes an explicit unit and computes b - a — both the argument order and the meaning of the result are reversed, so this one is worth double-checking every time rather than trusting muscle memory.

🔧 Try It Yourself

On a MySQL instance, run SELECT @@sql_mode; and check whether ONLY_FULL_GROUP_BY appears in the result. Then run SHOW CREATE TABLE against a table you use often and confirm its ENGINE= is InnoDB. Finally, write one query using NOW() and DATE_ADD(..., INTERVAL 1 DAY), translating from a T-SQL query you'd normally write with GETDATE() and DATEADD.

🙋 There Are No Dumb Questions

Q: If my MySQL server is on Linux, is there any way to make table names case-insensitive without changing every query?
A: Yes — the server-level setting lower_case_table_names controls this, but it can only be set at server initialization (changing it on an existing data directory isn't safely supported), and it doesn't fix the underlying issue if you ever move the database to a different OS or a managed cloud provider with a fixed setting. The far more portable fix is simply to standardize on one casing (lowercase snake_case is the MySQL convention) in every script and migration, so the setting never has to matter.

✅ Quick Check

A query with SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id; runs without error on one MySQL server but throws an error on another. What's the most likely explanation?

That's the last lesson — time for the recap, where all five lessons come together into one quick-reference summary.

← Back to