grammy-jiang/trojan_database_management.md

## trojan_database_management.md

      
    Raw
  

              trojan_database_management.md
            
          
    Create the table of users_summary:
CREATE OR REPLACE TABLE users_summary
(
    id                INT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id           INT       NOT NULL COMMENT 'the id in the table of users',
    created_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'the timestamp created this record',
    download          BIGINT UNSIGNED COMMENT 'the download in the table of users',
    upload            BIGINT UNSIGNED COMMENT 'the upload in the table of users',
    INDEX user_id__index (user_id) COMMENT 'the index of user_id',
    INDEX created_timestamp__index (created_timestamp DESC) COMMENT 'the index of created_timestamp',
    INDEX user_id_created_timestamp__index (user_id, created_timestamp DESC) COMMENT 'the compound index of user_id and created_timestamp'
)
    COMMENT 'Record the data usage of users';
An event record_users_summary, only save the records changed since last time:
CREATE OR REPLACE EVENT record_users_summary
    ON SCHEDULE EVERY 15 MINUTE
    COMMENT 'save the records changed since last time'
    DO INSERT INTO users_summary (user_id, download, upload)
       SELECT u.id, u.download, u.upload
       FROM users u
                JOIN (SELECT DISTINCT tbl.id
                      FROM (
                               SELECT u.id, u.download, u.upload
                               FROM users u
                               UNION ALL
                               SELECT us.user_id, us.download, us.upload
                               FROM users_summary us
                                        INNER JOIN (
                                   SELECT us.user_id, MAX(us.created_timestamp) AS latest_ts
                                   FROM users_summary us
                                   GROUP BY us.user_id
                               ) tm ON us.user_id = tm.user_id AND us.created_timestamp = tm.latest_ts
                           ) tbl
                      GROUP BY tbl.id, tbl.download, tbl.upload
                      HAVING COUNT(*) = 1
                      ORDER BY tbl.id) AS tbl
       WHERE u.id = tbl.id;
Remember to enable event scheduler:
SET GLOBAL event_scheduler = ON;
Check the event scheduler status:
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';

SELECT *
FROM information_schema.PROCESSLIST
WHERE USER = 'event_scheduler';
Create a new user:
SET @username = 'username';
SET @password = concat(@username, '@', 'password');
INSERT INTO users (username, password)
VALUES (@username, SHA2(@password, 224));