Redis vs MySQL for Shopping Carts: Why I Chose the Option That Can Lose Data

1. Clarify What Kind of Data Loss Is Acceptable

The claim "shopping carts can lose data" is often quoted without specifying what is lost. Losing a single add‑to‑cart action merely causes a minor user confusion, while losing an entire cart of weeks‑old items is a serious incident. The correct statement is that a cart can afford to lose writes from the last few seconds but cannot afford to lose stored inventory.

This boundary drives all subsequent technical choices: asynchronous disk flushing with a second‑level window is acceptable, but a pure in‑memory solution without persistence is not; brief loss during master‑slave failover is tolerable, but a full instance crash that erases data is not.

2. Data Model – Seven Fields Instead of Four

CREATE TABLE `cart_item` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    BIGINT UNSIGNED NOT NULL COMMENT 'who owns the cart',
  `sku_id`     BIGINT UNSIGNED NOT NULL COMMENT 'product selected',
  `quantity`   INT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'how many',
  `checked`    TINYINT      NOT NULL DEFAULT 1 COMMENT 'selected flag',
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'add time',
  `updated_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_sku` (`user_id`, `sku_id`),
  KEY `idx_user_created` (`user_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The four business‑relevant columns are sku_id, quantity, checked, and created_at. The other three (id, user_id, timestamps) are mandatory for any business table.

The unique key uk_user_sku enables an upsert in a single statement:

INSERT INTO cart_item (user_id, sku_id, quantity, checked)
VALUES (?, ?, ?, 1)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

Since MySQL 8.0.20 the VALUES() function is deprecated; the equivalent using row alias is:

INSERT INTO cart_item (user_id, sku_id, quantity, checked)
VALUES (?, ?, ?, 1) AS new
ON DUPLICATE KEY UPDATE quantity = quantity + new.quantity;

Without the unique key, a concurrent double‑click would cause duplicate rows, a bug that only appears in production.

Although the author advises not storing price, title, or image in the cart, many mature e‑commerce systems add a snapshot field for "price at add‑time" purely for display purposes (e.g., "price dropped 20元 since you added it"). This is a historical redundancy, not a consistency requirement.

3. Guest Cart Storage – Cookie vs LocalStorage

When a user adds items without logging in, the cart lives on the client. The debate is not about capacity (a 4KB cookie can hold ~170 items, far more than typical guest carts) but about bandwidth.

Cookie data is sent with every request to the same domain, inflating traffic for each page load. LocalStorage data is transmitted only when the application explicitly reads it and includes it in a request.

Therefore, if guest adds are infrequent and involve only a few items, Cookie is convenient because the server can read/write directly. If guests add many items, LocalStorage saves bandwidth.

4. Merging Guest and Logged‑In Carts

After login, the client pushes its local cart to the server, which writes it and clears the client cache. The challenge is conflict resolution when the server already has items.

Three real‑world strategies observed:

Prefer the local cart (assumes it reflects the latest user intent).

Take the maximum quantity per SKU (avoids under‑counting).

Sum the quantities (treats both sets as valid intents).

The choice depends on product price: high‑value items often use the maximum rule; low‑value items may sum. If the merged cart exceeds the system’s SKU limit (typically 100‑200), the author recommends truncating by oldest addition time and informing the user.

5. Storage Choice – Pure Redis, Pure MySQL, or Hybrid

Redis stores each user’s cart as a hash:

HSET cart:1001 2003481 '{"n":2,"c":1,"t":1753718400}'
HGETALL cart:1001
HDEL cart:1001 2003481

Using a hash allows per‑SKU updates without reading the whole cart, reducing overwrite conflicts in multi‑device scenarios.

Comparison of the three approaches:

Write throughput : Redis – ~100k QPS; MySQL – a few thousand QPS per instance; Hybrid – ~100k QPS.

Data‑loss risk : Redis – possible during AOF flush or replication lag; MySQL – essentially none; Hybrid – low.

Complex queries : Redis – not supported; MySQL – supports analytics; Hybrid – supports analytics.

Storage cost : Redis – high (in‑memory); MySQL – low (disk); Hybrid – high (both).

Operational complexity : Redis – low; MySQL – low; Hybrid – high (needs dual‑write consistency).

When to choose : Redis – high concurrency, no analytics; MySQL – need reporting; Hybrid – only at massive scale.

Most articles conclude that a hybrid gives the best of both worlds, but the author argues that the added consistency burden rarely justifies the cost for typical teams.

The decisive factor is whether the business will ask questions like "How many items were added to carts yesterday but not purchased?" If yes, MySQL is preferable because such queries are trivial with SQL but would require a separate pipeline with Redis.

If no such analytics are needed, a pure Redis solution with AOF every‑second persistence is sufficient, fitting the earlier "lose recent writes but not stored inventory" boundary.

6. Core Takeaways

The four decision points—data model size, guest‑cart storage, merge strategy, and backend storage—are not purely technical; they depend on business tolerance for data loss.

Typical tolerance hierarchy across e‑commerce tables:

Browsing history: can be discarded entirely (pure Redis or client‑side).

Shopping cart: can lose recent seconds, cannot lose accumulated items.

Order: must never be lost; requires transactional guarantees.

Payment flow: cannot be lost, must support reconciliation and replay.

Thus, when someone claims "core chain must use MySQL", the proper question is "which part of the chain is considered core?"