印刻数据库

外观

MySQL 数据库创建、修改与删除 ​

数据库创建 ​

基本语法 ​

sql
-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库并指定字符集
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS database_name;

字符集配置 ​

常用字符集 ​

  • utf8mb4：支持完整的 Unicode 字符集，包括表情符号
  • utf8：支持基本 Unicode 字符集
  • latin1：支持 Latin 字符集

字符集与排序规则 ​

sql
-- 查看可用的字符集
SHOW CHARACTER SET;

-- 查看可用的排序规则
SHOW COLLATION LIKE 'utf8mb4%';

-- 创建数据库时指定字符集和排序规则
CREATE DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

数据库选项 ​

数据文件位置 ​

sql
-- 在指定位置创建数据库（需要相应权限）
CREATE DATABASE database_name 
DATA DIRECTORY = '/path/to/data' 
INDEX DIRECTORY = '/path/to/index';

存储引擎 ​

sql
-- 设置默认存储引擎
SET default_storage_engine = InnoDB;

-- 创建数据库后设置默认存储引擎
ALTER DATABASE database_name 
DEFAULT STORAGE ENGINE = InnoDB;

权限设置 ​

创建数据库并授权 ​

sql
-- 创建数据库
CREATE DATABASE database_name;

-- 授权用户访问数据库
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost' IDENTIFIED BY 'password';

-- 刷新权限
FLUSH PRIVILEGES;

权限类型 ​

  • ALL PRIVILEGES：所有权限
  • SELECT：查询权限
  • INSERT：插入权限
  • UPDATE：更新权限
  • DELETE：删除权限
  • CREATE：创建表和索引权限
  • ALTER：修改表结构权限
  • DROP：删除表权限

数据库修改 ​

修改数据库字符集 ​

sql
-- 修改数据库字符集
ALTER DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

修改数据库默认存储引擎 ​

sql
-- 修改默认存储引擎
ALTER DATABASE database_name 
DEFAULT STORAGE ENGINE = InnoDB;

修改数据库选项 ​

sql
-- 修改数据库选项
ALTER DATABASE database_name 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_unicode_ci 
DEFAULT STORAGE ENGINE = InnoDB;

重命名数据库 ​

使用 RENAME DATABASE（不推荐） ​

sql
-- MySQL 5.1.7 到 5.1.23 支持，已废弃
RENAME DATABASE old_name TO new_name;

推荐方法 ​

sql
-- 1. 创建新数据库
CREATE DATABASE new_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2. 导出旧数据库
mysqldump -u root -p old_database > old_database.sql;

-- 3. 导入到新数据库
mysql -u root -p new_database < old_database.sql;

-- 4. 删除旧数据库（可选）
DROP DATABASE old_database;

数据库删除 ​

基本语法 ​

sql
-- 删除数据库
DROP DATABASE database_name;

-- 删除数据库（如果存在）
DROP DATABASE IF EXISTS database_name;

删除前的准备 ​

备份数据 ​

bash
-- 备份整个数据库
mysqldump -u root -p database_name > database_backup.sql;

-- 备份数据库结构
mysqldump -u root -p --no-data database_name > database_schema.sql;

检查数据库依赖 ​

sql
-- 检查数据库中的表
SHOW TABLES FROM database_name;

-- 检查数据库中的视图、存储过程等
SHOW FULL TABLES FROM database_name WHERE TABLE_TYPE LIKE 'VIEW';
SHOW PROCEDURE STATUS WHERE Db = 'database_name';
SHOW FUNCTION STATUS WHERE Db = 'database_name';

移除用户权限 ​

sql
-- 查看用户权限
SHOW GRANTS FOR 'user'@'localhost';

-- 撤销用户权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

数据库管理最佳实践 ​

创建数据库 ​

命名规范 ​

  • 使用小写字母
  • 使用下划线分隔单词
  • 避免使用保留字
  • 保持名称简洁明了
  • 遵循业务命名规范

字符集选择 ​

  • 推荐使用 utf8mb4 字符集
  • 选择合适的排序规则
  • 保持数据库字符集一致

权限管理 ​

  • 遵循最小权限原则
  • 为不同用户分配不同权限
  • 定期审查权限

修改数据库 ​

变更管理 ​

  • 制定变更计划
  • 测试变更影响
  • 执行变更前备份
  • 记录变更历史

性能考虑 ​

  • 避免在高峰期执行变更
  • 考虑变更对性能的影响
  • 监控变更后的系统状态

删除数据库 ​

安全措施 ​

  • 确认删除操作
  • 执行前备份
  • 检查依赖关系
  • 移除相关权限

清理工作 ​

  • 删除相关备份
  • 清理相关配置
  • 更新文档
  • 通知相关人员

数据库信息查询 ​

查看数据库列表 ​

sql
-- 查看所有数据库
SHOW DATABASES;

-- 查看当前数据库
SELECT DATABASE();

-- 查看数据库大小
SELECT table_schema AS 'Database', 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
GROUP BY table_schema;

查看数据库详情 ​

sql
-- 查看数据库创建语句
SHOW CREATE DATABASE database_name;

-- 查看数据库字符集
SELECT schema_name, default_character_set_name, default_collation_name 
FROM information_schema.schemata 
WHERE schema_name = 'database_name';

查看数据库表 ​

sql
-- 查看数据库中的表
SHOW TABLES FROM database_name;

-- 查看表结构
DESCRIBE database_name.table_name;

数据库操作脚本 ​

创建数据库脚本 ​

bash
#!/bin/bash

# 数据库信息
DB_NAME="new_database"
DB_USER="root"
DB_PASS="password"
CHARSET="utf8mb4"
COLLATION="utf8mb4_unicode_ci"

# 创建数据库
mysql -u "$DB_USER" -p"$DB_PASS" -e "CREATE DATABASE IF NOT EXISTS $DB_NAME CHARACTER SET $CHARSET COLLATE $COLLATION;"

# 授权用户
mysql -u "$DB_USER" -p"$DB_PASS" -e "GRANT ALL PRIVILEGES ON $DB_NAME.* TO 'app_user'@'localhost' IDENTIFIED BY 'app_password';"

# 刷新权限
mysql -u "$DB_USER" -p"$DB_PASS" -e "FLUSH PRIVILEGES;"

echo "Database $DB_NAME created successfully!"

备份并删除数据库脚本 ​

bash
#!/bin/bash

# 数据库信息
DB_NAME="old_database"
DB_USER="root"
DB_PASS="password"
BKP_DIR="/path/to/backup"
DATE=$(date +"%Y%m%d_%H%M%S")

# 创建备份目录
mkdir -p "$BKP_DIR"

# 备份数据库
echo "Backing up database $DB_NAME..."
mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" > "$BKP_DIR/${DB_NAME}_${DATE}.sql"

if [ $? -eq 0 ]; then
    echo "Backup successful!"
    
    # 确认删除
    read -p "Are you sure you want to delete database $DB_NAME? (y/n): " CONFIRM
    
    if [ "$CONFIRM" = "y" ]; then
        # 删除数据库
        echo "Deleting database $DB_NAME..."
        mysql -u "$DB_USER" -p"$DB_PASS" -e "DROP DATABASE IF EXISTS $DB_NAME;"
        
        if [ $? -eq 0 ]; then
            echo "Database $DB_NAME deleted successfully!"
        else
            echo "Failed to delete database!"
        fi
    else
        echo "Deletion cancelled."
    fi
else
    echo "Backup failed!"
    exit 1
fi

不同版本的特性 ​

MySQL 5.7 ​

  • 增强了字符集支持
  • 改进了权限管理
  • 引入了更多的数据库选项

MySQL 8.0 ​

  • 引入了角色管理
  • 增强了密码验证
  • 改进了字符集和排序规则
  • 支持原子 DDL 语句

MariaDB ​

  • 增强了数据库管理功能
  • 提供了更多的存储引擎选项
  • 改进了性能和可靠性

性能考虑 ​

创建数据库 ​

  • 选择合适的字符集
  • 考虑数据文件位置
  • 配置适当的存储引擎
  • 避免创建过多数据库

修改数据库 ​

  • 注意字符集修改的影响
  • 考虑存储引擎变更的开销
  • 监控修改后的性能

删除数据库 ​

  • 清理相关资源
  • 释放存储空间
  • 监控删除后的系统状态

常见问题（FAQ） ​

Q1: 如何选择合适的字符集？ ​

A1: 选择字符集时应考虑：

  • 应用程序支持的字符集
  • 需要存储的字符类型
  • 性能影响
  • 兼容性要求
  • 推荐使用 utf8mb4 字符集，支持完整的 Unicode 字符集

Q2: 如何安全地重命名数据库？ ​

A2: 安全重命名数据库的步骤：

  1. 创建新数据库
  2. 导出旧数据库
  3. 导入到新数据库
  4. 验证数据完整性
  5. 更新应用配置
  6. 旧数据库备份保留一段时间后删除

Q3: 如何授权用户访问数据库？ ​

A3: 授权用户访问数据库的步骤：

  • 创建用户（如果不存在）
  • 授予适当的权限
  • 刷新权限
  • 测试用户访问

Q4: 如何查看数据库大小？ ​

A4: 可以使用以下查询查看数据库大小：

sql
SELECT table_schema AS 'Database', 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
GROUP BY table_schema;

Q5: 如何在删除数据库前备份数据？ ​

A5: 删除数据库前备份数据的方法：

  • 使用 mysqldump 备份整个数据库
  • 备份到安全的位置
  • 验证备份的完整性
  • 保留备份一段时间

Q6: 如何处理数据库创建失败的情况？ ​

A6: 处理数据库创建失败的方法：

  • 检查权限是否足够
  • 检查磁盘空间是否充足
  • 检查数据库名是否合法
  • 检查字符集是否支持
  • 查看错误日志获取详细信息

Q7: 如何批量创建多个数据库？ ​

A7: 批量创建多个数据库的方法：

  • 使用脚本循环创建
  • 从文件读取数据库名
  • 执行批量创建语句

Q8: 如何限制用户只能访问特定数据库？ ​

A8: 限制用户只能访问特定数据库的方法：

  • 只授予用户对特定数据库的权限
  • 不授予全局权限
  • 定期审查权限
  • 使用角色管理（MySQL 8.0+）