Contents
I needed user source and gender for reporting. Gender lived in third-party OAuth JSON in
user_info. I did not want to parse it in PHP, so I pushed the work into MySQL. Tell me if you have a cleaner approach.
Setup (simplified)
Schema
OAuth bindings for Weibo, QQ, and WeChat; profile blob in user_info.
user_platformweibo_id,qq_id,weixin_id,user_info,uid,user_platform_id
Gender encodings
- Weibo
gender:m/f/n - QQ
gender: localized male/female labels in the payload (not ASCIIm/f) - WeChat
sex:1/2
Constraints
- On the order of a million rows — queries had to stay practical on production hardware.
User source
Accounts are not strictly one-to-one with users, so non-empty third-party IDs imply the channel. Bindings were unique enough for this exercise (schema quirks skipped).
1 | SELECT IF( |
ISNULL(weixin_id) || weixin_id != '' covers NULL and empty string; ISNULL(weixin_id) || LENGTH(TRIM(weixin_id)) < 1 works too.
Gender extraction
- Two payload shapes (
gendervssex) - Stage into a temp table as unified
sex(1male,2female,0unknown) - Add
sexon the user table and update from the temp table
LOCATE and MID
WeChat example — detect whether sex is present:
1 | SELECT locate('sex":', user_info, 50) AS is_sex |
First attempt — extract in one pass:
1 | SELECT locate('sex":', user_info, 50) AS is_sex, |
That was painfully slow: locate ran twice per row. Compute position once, then join:
1 | SELECT mid(user_info, is_sex + 5, 1) AS sex, uid |
If you already know where to start searching, pick a minimum offset from the data and pass it as LOCATE’s third argument:
1 | SELECT MIN(is_sex) |
Minimum position was 57; I used locate('sex":', user_info, 55) to stay safe.
On my laptop: 10.17 s → 7.36 s.
No convenient one-step SELECT … INTO for this case — create a table, then INSERT … SELECT:
1 | CREATE TABLE user_sex ( |
Same pattern for Weibo and QQ, but match on gender. Normalize values with REPLACE:
1 | UPDATE user_sex SET sex = replace(sex, UNHEX('E794B7'), 1); |
Drop the staging table when done:
1 | DROP TABLE user_sex; |
You can still filter earlier — e.g. skip uid = 0 (unregistered) — depending on product rules.
Takeaway: adding a sex column on the user table held locks the longest; the temp-table steps and heavy SELECTs were comparatively short. Staging also keeps long-running reads off the final user-table update — same old trick of splitting one big job into smaller pieces.
MySQL
--comments need a space: use--not--.
For more tuning signals, use the slow query log:
1
2
3
4
5
6
7
8 set GLOBAL long_query_time=1;
set GLOBAL log_slow_queries=ON;
set GLOBAL log_queries_not_using_indexes=1;
show variables like '%slow%';
show variables like '%long_query%';
-- log to table:
set global log_output='TABLE';
select * from mysql.slow_log;
Questions? Leave a comment below or email: newbvirgil@gmail.com![]()
