MySQL — pulling gender from OAuth JSON with LOCATE and MID

Contents

  1. 1. Setup (simplified)
    1. 1.1. Schema
    2. 1.2. Gender encodings
    3. 1.3. Constraints
  2. 2. User source
  3. 3. Gender extraction
    1. 3.1. LOCATE and MID

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_platform
    • weibo_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 ASCII m/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
2
3
4
5
6
7
8
9
10
11
12
SELECT IF(
(ISNULL(weixin_id) || weixin_id != ''),
IF(
(ISNULL(weibo_id) || weibo_id != ''),
IF((ISNULL(qq_id) || qq_id != ''), 'mobile', 'QQ'),
'Weibo'
),
'WeChat'
) AS user_source,
uid
FROM user_platform
WHERE uid != 0;

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 (gender vs sex)
  • Stage into a temp table as unified sex (1 male, 2 female, 0 unknown)
  • Add sex on the user table and update from the temp table

LOCATE and MID

WeChat example — detect whether sex is present:

1
2
3
4
SELECT locate('sex":', user_info, 50) AS is_sex
FROM user_platform
WHERE user_info != ''
HAVING is_sex != 0;

First attempt — extract in one pass:

1
2
3
4
5
SELECT locate('sex":', user_info, 50) AS is_sex,
mid(user_info, locate('sex":', user_info, 50) + 5, 1) AS sex
FROM user_platform
WHERE user_info != ''
HAVING is_sex != 0;

That was painfully slow: locate ran twice per row. Compute position once, then join:

1
2
3
4
5
6
7
8
9
10
11
SELECT mid(user_info, is_sex + 5, 1) AS sex, uid
FROM user_platform p,
(
SELECT locate('sex":', user_info, 50) AS is_sex, user_platform_id
FROM user_platform
WHERE user_info != ''
AND uid != 0
HAVING is_sex != 0
) t
WHERE t.user_platform_id = p.user_platform_id
GROUP BY 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
2
3
4
5
6
7
SELECT MIN(is_sex)
FROM (
SELECT locate('sex":', user_info) AS is_sex
FROM user_platform
WHERE user_info != ''
HAVING is_sex != 0
) t;

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE user_sex (
uid int(11) NOT NULL,
sex char(1) NOT NULL,
PRIMARY KEY (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user_sex (uid, sex)
SELECT uid, sex
FROM (
SELECT mid(user_info, is_sex + 5, 1) AS sex, uid
FROM user_platform p,
(
SELECT locate('sex":', user_info, 55) AS is_sex, user_platform_id
FROM user_platform
WHERE user_info != ''
AND uid != 0
HAVING is_sex != 0
) t
WHERE t.user_platform_id = p.user_platform_id
GROUP BY uid
) tmp;

Same pattern for Weibo and QQ, but match on gender. Normalize values with REPLACE:

1
2
3
4
5
6
UPDATE user_sex SET sex = replace(sex, UNHEX('E794B7'), 1);
UPDATE user_sex SET sex = replace(sex, UNHEX('E597B3'), 2);
UPDATE user_sex SET sex = replace(sex, 'm', 1);
UPDATE user_sex SET sex = replace(sex, 'f', 2);
UPDATE user_sex SET sex = replace(sex, '"', 0);
-- catches gender:"" style empties

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