Database Overview

Database Information

• Database Type: MySQL 8.0+
• Character Set: utf8mb4
• Collation: utf8mb4_unicode_ci
• Table Count: 14 core tables
• Initialization Script: server/database/schema.sql

Table Structure Overview

Basic Tables (No Foreign Key Dependencies)

Table Name	Description	Key Fields
users	User table	id, username, email, password, role, status
categories	Category table	id, name, slug, color, icon, sort_order
skill_categories	Skill category table	id, name, icon, color, sort_order
files	File management table	id, file_name, file_path, file_size, storage_type
settings	System settings table	id, group_name, key_name, key_value
resource_categories	Resource category table	id, name, slug, icon, color

Intermediate Layer Tables (Depend on Basic Tables)

Table Name	Description	Related Tables
skills	Skill items table	skill_categories
articles	Article table	users, categories
projects	Project table	files
carousels	Carousel table	-
galleries	Gallery table	-
wall_messages	Message table	-

Advanced Layer Tables (Depend on Intermediate Layer Tables)

Table Name	Description	Related Tables
visit_hourly_statistics	Hourly visit statistics table	-
gallery_images	Gallery image table	galleries, files
resources	Resource table	resource_categories, files

Database Relationship Diagram

users (1) ----< (n) articles
categories (1) ----< (n) articles

skill_categories (1) ----< (n) skills

galleries (1) ----< (n) gallery_images
files (1) ----< (n) gallery_images

projects (1) ----< (1) files (cover_image)

resource_categories (1) ----< (n) resources
files (1) ----< (n) resources
files (1) ----< (1) resources (cover_image)

Initialize Database

Method 1: Using SQL Script

mysql -u root -p < server/database/schema.sql

Method 2: Using Node Script

pnpm run db:init

Default Data

Default Administrator Account

Field	Value
Username	admin
Password	123456 (bcrypt encrypted)
Email	admin@demo.com
Nickname	Demo Administrator
Role	admin
Status	active

Default Category Data

System pre-configures 5 categories:

1. Open Source Projects
2. Company Projects
3. Personal Projects
4. Study Notes
5. Small Tools

Default Skill Categories

System pre-configures 5 skill categories:

1. Frontend Development
2. Backend Development
3. Database
4. Development Tools & Deployment
5. Mobile Development

Default Skill Data

System pre-configures 50+ skill items, covering frontend, backend, database, development tools, and other areas.

Default System Settings

• Total Visits: 0
• Today's Visits: 0
• Last Reset Date: Current date
• Storage Type: local
• Analytics Code: Empty

Database Maintenance

Backup Database

mysqldump -u root -p nuxt_blog > backup_$(date +%Y%m%d).sql

Restore Database

mysql -u root -p nuxt_blog < backup_20250124.sql

Optimize Database

mysql -u root -p nuxt_blog -e "OPTIMIZE TABLE *"

Common Queries

View All Tables

SHOW TABLES;

View Table Structure

DESCRIBE users;
-- or
SHOW COLUMNS FROM users;

View Table Indexes

SHOW INDEX FROM users;

View Foreign Key Constraints

SELECT 
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
  TABLE_SCHEMA = 'nuxt_blog'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Performance Optimization Recommendations

1. Index Optimization

Indexes for main tables:

• users: username, email
• categories: slug, sort_order
• articles: status, is_featured, created_at, author_id, category_id
• projects: category, sort_order
• galleries: status, is_featured, sort_order

2. Query Optimization

• Use EXPLAIN to analyze query performance
• Avoid SELECT *
• Reasonably use LIMIT for pagination
• Use indexed fields for filtering

3. Regular Maintenance

-- Analyze tables
ANALYZE TABLE users, articles, projects;

-- Optimize tables
OPTIMIZE TABLE users, articles, projects;

-- Check tables
CHECK TABLE users, articles, projects;

Important Notes

1. Foreign Key Constraints: Some tables have foreign key constraints, pay attention to dependencies when deleting data
2. Character Set: Use utf8mb4 to support full Unicode characters
3. Timestamps: Use TIMESTAMP type to automatically record creation and update times
4. Soft Deletes: Most tables use status field to implement soft deletes
5. Indexes: Key fields already have indexes to improve query performance

Detailed Documentation

• Table Structure
• Field Descriptions