面试题：MySQL命令行实用程序的高级脚本自动化

Question

编写一段MySQL命令行脚本，实现以下功能：自动创建一个新的数据库，并在该数据库中创建多个具有外键关联的表，同时插入一些测试数据。要求脚本具备一定的错误处理机制，能够在出现错误时给出合理的提示信息。并且说明如何将这个脚本集成到日常的数据库维护任务中，例如通过定时任务调度。

Answer 1

-- 创建数据库并处理错误
CREATE DATABASE IF NOT EXISTS new_database;
-- 检查数据库是否创建成功
IF NOT EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'new_database') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '创建数据库失败';
END IF;

-- 使用新创建的数据库
USE new_database;

-- 创建表并处理错误
-- 创建主表
CREATE TABLE IF NOT EXISTS main_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB;
-- 检查主表是否创建成功
IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'new_database' AND TABLE_NAME ='main_table') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '创建主表失败';
END IF;

-- 创建从表并设置外键关联
CREATE TABLE IF NOT EXISTS sub_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    main_id INT,
    description VARCHAR(255),
    FOREIGN KEY (main_id) REFERENCES main_table(id)
) ENGINE=InnoDB;
-- 检查从表是否创建成功
IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'new_database' AND TABLE_NAME ='sub_table') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '创建从表失败';
END IF;

-- 插入测试数据并处理错误
-- 向主表插入数据
INSERT INTO main_table (name) VALUES ('测试数据1');
-- 检查插入是否成功
IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '向主表插入数据失败';
END IF;

-- 获取主表插入的id
SET @main_id = LAST_INSERT_ID();

-- 向从表插入数据
INSERT INTO sub_table (main_id, description) VALUES (@main_id, '子表关联测试数据');
-- 检查插入是否成功
IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '向从表插入数据失败';
END IF;

将脚本集成到日常数据库维护任务中

通过定时任务调度（以Linux系统为例）

1. 编写脚本文件：将上述MySQL命令行脚本保存为一个.sql文件，例如create_database_and_tables.sql。
2. 编写Shell脚本：创建一个Shell脚本用于执行MySQL脚本，并处理可能的错误。例如，创建run_mysql_script.sh：

#!/bin/bash
mysql -u your_username -pyour_password < create_database_and_tables.sql
if [ $? -ne 0 ]; then
    echo "执行MySQL脚本时出现错误"
fi

3. 设置定时任务：使用crontab -e命令编辑当前用户的定时任务计划。例如，如果你希望每天凌晨2点执行该任务，可以添加如下一行：

0 2 * * * /path/to/run_mysql_script.sh

这样就可以将该脚本集成到日常的数据库维护任务中，通过定时任务进行调度执行。请根据实际的MySQL用户名、密码和文件路径进行相应调整。