CODEWITHSUNDEEP
mysqlsql
OpiF
OpiF

Reputation: 113

Using UNIQUE indices with NULL fields in MySQL?

I can check, periodically, for a list of users that are currently online. I want to turn this into something useful like a list of entries per user with login / logout time. There is no other way to determine this information apart from checking who is currently online.
After some thinking I came up with something like this:

CREATE TABLE onlineActivity (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR (32) NOT NULL,
    login_time DATETIME NOT NULL,
    logout_time DATETIME NOT NULL,
    time SMALLINT (3) NOT NULL DEFAULT 0,
    online BOOL DEFAULT NULL,
    UNIQUE (name, online),
    PRIMARY KEY (id)
)   ENGINE = MyISAM;

I run this query every few minutes to add/update names in the activity list:

INSERT INTO onlineActivity (name, login_time, logout_time, online)
    SELECT name, now(), now(), true FROM onlineList ON DUPLICATE KEY UPDATE logout_time = now()

And this query is run for every user that has logged out:
(the names are determined by comparing two adjacent online lists, the current one and the previous one)

UPDATE onlineActivity SET online = NULL WHERE name = ? AND online = 1

The questions:

I want to store a full history of user activity, not a single entry.

Upvotes: 3

Views: 222

Answers (1)

dunos
dunos

Reputation: 755

I am not sure why you have a unique on name and online since what you are trying to do is create a list of online activity. Putting a unique key as you have specified will mean that you can only have a name in there three times, one for each state (null, true, false).

What you are effectively doing is trying to create a history table in which case to use your current method of populating the table you should put a unique key on (name, logout_time) with a null logout_time indicating a currently logged in user (since you would only want one logout time that is null).

Something like this:

CREATE TABLE onlineActivity (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR (32) NOT NULL,
    login_time DATETIME NOT NULL,
    logout_time DATETIME NULL,
    time SMALLINT (3) NOT NULL DEFAULT 0,
    online BOOL not null DEFAULT false,
    UNIQUE (name, logout_time),
    PRIMARY KEY (id)
)   ENGINE = MyISAM;

Then run this on a schedule to update the table

INSERT IGNORE INTO onlineActivity (name, login_time, logout_time, online)
    SELECT name, now(), null, true FROM onlineList

And this on user logout

UPDATE onlineActivity SET online = false, logout_time = now() WHERE name = ? AND logout_time = null

Upvotes: 1

Related Questions