2-4、数据库初始化与SQL脚本导入

项目根目录/
  sql/
    init.sql          (建库建表脚本)
    data.sql          (初始数据)
    update_v1.1.sql   (版本更新脚本)
  doc/
    database.sql
  src/main/resources/
    sql/
      init.sql

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/my_project?...

mysql -u root -p

-- 创建数据库
CREATE DATABASE IF NOT EXISTS my_project 
  DEFAULT CHARACTER SET utf8mb4 
  COLLATE utf8mb4_general_ci;

-- 确认创建成功
SHOW DATABASES;

-- 使用这个数据库
USE my_project;

# 方式1：在数据库外部执行
mysql -u root -p my_project < D:/projects/sql/init.sql

# 方式2：先登录再执行
mysql -u root -p
USE my_project;
SOURCE D:/projects/sql/init.sql;

1. init.sql          → 先建库建表
2. data.sql          → 再插入初始数据
3. update_xxx.sql    → 按版本号顺序执行更新脚本

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC user;
-- 或
SHOW CREATE TABLE user;

-- 查看每张表的记录数
SELECT COUNT(*) FROM user;
SELECT COUNT(*) FROM role;
SELECT COUNT(*) FROM menu;

-- 查看数据内容
SELECT * FROM user LIMIT 10;

-- 查看所有表的字符集
SELECT TABLE_NAME, TABLE_COLLATION 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'my_project';

-- 修改表字符集
ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ERROR 1050 (42S01): Table 'user' already exists

DROP TABLE IF EXISTS user;

ERROR 1054 (42S22): Unknown column 'xxx' in 'field list'

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'my_project'

-- 给用户授权
GRANT ALL PRIVILEGES ON my_project.* TO 'root'@'%';
FLUSH PRIVILEGES;

ERROR 1406 (22001): Data too long for column 'name' at row 1

ALTER TABLE user MODIFY COLUMN name VARCHAR(255);

mysql -u root -p --default-character-set=utf8mb4 my_project < init.sql

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

-- 关闭外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 执行你的SQL
SOURCE init.sql;

-- 开启外键检查
SET FOREIGN_KEY_CHECKS = 1;

ERROR 1064 (42000): You have an error in your SQL syntax

INSERT INTO user (`order`, `level`) VALUES (1, 10);

mysql -u root -p
USE my_project;
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
SOURCE D:/projects/sql/big_data.sql;
COMMIT;
SET unique_checks = 1;
SET foreign_key_checks = 1;

V1__init.sql
V2__add_user_table.sql
V3__add_user_phone_column.sql

src/main/resources/db/changelog/
  db.changelog-master.xml
  db.changelog-1.0.xml