Geek asks and answers

Using UNIQUE indexes with NULL fields in MySQL?

I can periodically check the list of users who are currently online. I want to turn this into something useful, for example, a list of entries for each user with login / logout times. There is no other way to determine this information than checking who is currently online.
After 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 action 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 request is executed for each user who logs out of the system:
(names are determined by comparing two adjacent online lists, the current and the previous)

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

Questions:

  • , NULL (online) UNIQUE - . , MySQL online ( ) , , NULL. - , ? , MySQL .
  • (PostgreSQL, SQLite) , MySQL?
  • , , , ?
  • , . ?
  • 300 ~ 500 . , ?

, .

+3
1

, , , , - -. , , , , (null, true, false).

, , - , (name, logout_time) logout_time, (, , null).

- :

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;

,

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


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

More articles:


All Articles