平时有接触一些搭建私服游戏的内容，稍稍精通后也是弄了些一键搭建的脚本。为了防止滥用搞了个验证系统

脚本网站：https://ym.senru.site/

一、基础表结构

1. 脚本表 (allowed_scripts)

CREATE TABLE allowed_scripts (
id int(11) NOT NULL AUTO_INCREMENT,
script_id varchar(100) NOT NULL,
script_name varchar(255) NOT NULL,
max_daily_usage int(11) DEFAULT 3,
description text DEFAULT NULL,
is_active tinyint(1) DEFAULT 1,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY script_id (script_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 脚本使用记录表 (script_usage)

CREATE TABLE script_usage (
id int(11) NOT NULL AUTO_INCREMENT,
script_id varchar(100) NOT NULL,
script_name varchar(255) NOT NULL,
client_ip varchar(45) NOT NULL,
usage_date date NOT NULL,
usage_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY script_id (script_id),
KEY client_ip (client_ip),
KEY usage_date (usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 每日白名单表 (daily_whitelist)

CREATE TABLE daily_whitelist (
id int(11) NOT NULL AUTO_INCREMENT,
ip_address varchar(45) NOT NULL,
description varchar(255) DEFAULT NULL,
expires_at date NOT NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY ip_address (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. 永久白名单表 (permanent_whitelist)

CREATE TABLE permanent_whitelist (
id int(11) NOT NULL AUTO_INCREMENT,
ip_address varchar(45) NOT NULL,
description varchar(255) DEFAULT NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY ip_address (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5. 系统设置表 (system_settings)

CREATE TABLE system_settings (
id int(11) NOT NULL AUTO_INCREMENT,
setting_key varchar(100) NOT NULL,
setting_value text DEFAULT NULL,
description varchar(255) DEFAULT NULL,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY setting_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

6. 系统日志表 (system_logs) 

CREATE TABLE system_logs (
id int(11) NOT NULL AUTO_INCREMENT,
action varchar(100) NOT NULL,
description text DEFAULT NULL,
ip_address varchar(45) DEFAULT NULL,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY created_at (created_at),
KEY action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二、完整的数据库初始化脚本

1. init_database_final.sql脚本代码

-- 脚本验证系统 - 最终版数据库初始化脚本
-- 保存为：init_database_final.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. 创建系统设置表
DROP TABLE IF EXISTS `system_settings`;
CREATE TABLE `system_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入默认系统设置
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `description`) VALUES
('max_daily_usage_default', '3', '默认每日使用次数限制'),
('auto_cleanup_days', '30', '自动清理多少天前的数据'),
('system_name', '脚本验证系统', '系统名称'),
('admin_email', 'admin@example.com', '管理员邮箱'),
('enable_notifications', '1', '是否启用通知'),
('retention_period', '365', '数据保留天数'),
('theme', 'default', '系统主题'),
('language', 'zh-CN', '系统语言'),
('api_rate_limit', '100', 'API速率限制（每分钟）'),
('maintenance_mode', '0', '维护模式开关'),
('log_retention_days', '90', '日志保留天数');

-- 2. 创建系统日志表
DROP TABLE IF EXISTS `system_logs`;
CREATE TABLE `system_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. 检查并创建其他表（如果不存在）

-- 3.1 脚本表
DROP TABLE IF EXISTS `allowed_scripts`;
CREATE TABLE `allowed_scripts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `script_id` varchar(100) NOT NULL,
  `script_name` varchar(255) NOT NULL,
  `max_daily_usage` int(11) DEFAULT 3,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `script_id` (`script_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3.2 脚本使用记录表
DROP TABLE IF EXISTS `script_usage`;
CREATE TABLE `script_usage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `script_id` varchar(100) NOT NULL,
  `script_name` varchar(255) NOT NULL,
  `client_ip` varchar(45) NOT NULL,
  `usage_date` date NOT NULL,
  `usage_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `script_id` (`script_id`),
  KEY `client_ip` (`client_ip`),
  KEY `usage_date` (`usage_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3.3 每日白名单表
DROP TABLE IF EXISTS `daily_whitelist`;
CREATE TABLE `daily_whitelist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `expires_at` date NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_address` (`ip_address`),
  KEY `expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3.4 永久白名单表
DROP TABLE IF EXISTS `permanent_whitelist`;
CREATE TABLE `permanent_whitelist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_address` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. 添加必要的索引（使用安全的方式）
-- 检查并创建索引的存储过程
DROP PROCEDURE IF EXISTS `CreateIndexIfNotExists`;
DELIMITER //
CREATE PROCEDURE `CreateIndexIfNotExists`(
    IN tableName VARCHAR(64),
    IN indexName VARCHAR(64),
    IN indexColumns VARCHAR(255)
)
BEGIN
    DECLARE indexExists INT DEFAULT 0;
    
    -- 检查索引是否存在
    SELECT COUNT(1) INTO indexExists
    FROM information_schema.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = tableName 
    AND INDEX_NAME = indexName;
    
    -- 如果索引不存在，则创建
    IF indexExists = 0 THEN
        SET @sql = CONCAT('CREATE INDEX ', indexName, ' ON `', tableName, '`(', indexColumns, ')');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END//
DELIMITER ;

-- 创建所有必要的索引
CALL CreateIndexIfNotExists('script_usage', 'idx_script_usage_script_date', 'script_id, usage_date');
CALL CreateIndexIfNotExists('allowed_scripts', 'idx_allowed_scripts_active', 'is_active');
CALL CreateIndexIfNotExists('daily_whitelist', 'idx_daily_whitelist_expires', 'expires_at');
CALL CreateIndexIfNotExists('system_logs', 'idx_system_logs_action', 'action');

-- 删除临时存储过程
DROP PROCEDURE IF EXISTS `CreateIndexIfNotExists`;

-- 5. 记录初始化日志
INSERT INTO `system_logs` (`action`, `description`, `ip_address`) 
VALUES ('system', '数据库初始化完成', 'system');

SET FOREIGN_KEY_CHECKS = 1;

-- 显示创建结果
SELECT '数据库表创建完成!' AS `message`;
SELECT 
    TABLE_NAME AS `表名`,
    TABLE_ROWS AS `行数`,
    CREATE_TIME AS `创建时间`
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
ORDER BY TABLE_NAME;

2.使用MySQL命令行执行初始化脚本

# 连接到数据库
mysql -h 数据库地址 -P 端口 -u 用户名 -p 数据库名

# 执行初始化脚本
mysql> source init_database_final.sql;

3.使用PHP脚本执行执行初始化脚本

<?php
// init_db.php - 数据库初始化脚本
$db_host = '数据库地址:端口';
$db_name = '数据库名';
$db_user = '用户名';
$db_pass = '数据库密码4';

try {
    $db = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 读取SQL文件
    $sql = file_get_contents('init_database_final.sql');
    
    // 分割SQL语句
    $statements = explode(';', $sql);
    
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (!empty($statement)) {
            try {
                $db->exec($statement);
            } catch (PDOException $e) {
                // 忽略重复创建表的错误
                if (strpos($e->getMessage(), 'already exists') === false) {
                    echo "错误: " . $e->getMessage() . "\n";
                }
            }
        }
    }
    
    echo "数据库初始化成功！\n";
    
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}
?>

#执行
php init_db.php