Các thao tác cơ bản với database

Tạo cơ sở dữ liệu mới:

CREATE DATABASE ten_khoi_du_lieu;

Xóa cơ sở dữ liệu (cẩn thận!):

DROP DATABASE ten_khoi_du_lieu;

Đặt lại giá trị tự tăng cho cột ID:

ALTER TABLE ten_bang AUTO_INCREMENT = 1;

Thao tác nhanh qua công cụ như Navicat

Chọn bảng cần xử lý → Chuột phải → Chọn Truncate Table. Hành động này sẽ xóa toàn bộ dữ liệu trong bảng đồng thời reset lại giá trị tự tăng về 1.

Xuất và nhập dữ liệu bằng dòng lệnh

Đầu tiên, truy cập vào cơ sở dữ liệu mong muốn:

SOURCE duong_dan_den_file_sql.sql;

Xuất toàn bộ dữ liệu từ tất cả các cơ sở dữ liệu thành một file:

mysqldump -h127.0.0.1 -P3306 -uroot -p222 -A > D:/haha/all.sql

Xuất riêng một cơ sở dữ liệu (ví dụ: aaa):

mysqldump -h127.0.0.1 -P3306 -uroot -p222 aaa > D:/hahaha/aaa.sql

Chọn bộ ký tự và sắp xếp phù hợp khi tạo cơ sở dữ liệu

Khi sử dụng Navicat để tạo cơ sở dữ liệu, nên thiết lập:

• Charset: utf8mb3
• Collation: utf8mb3_general_ci

Nếu chọn utf8mb4, mặc định hệ thống sẽ dùng utf8mb4_0900_ai_ci. Điều này có thể gây lỗi khi khôi phục dữ liệu lên máy chủ khác nếu cơ sở dữ liệu đích mặc định là utf8mb3.

Do đó, khi tạo cơ sở dữ liệu bằng lệnh:

CREATE DATABASE sina DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Không được dùng utf8mb4_0900_ai_ci vì sẽ không thể tạo được cơ sở dữ liệu.

Khôi phục file SQL trên máy chủ từ xa

Trước tiên chuyển đến cơ sở dữ liệu đích:

USE ten_co_so_du_lieu;

Rồi thực thi file SQL:

SOURCE ten_file.sql;

Nếu gặp lỗi "File not found", kiểm tra nội dung file SQL, đặc biệt là phần định nghĩa charset và collation. Nếu thấy:

CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci

→ Cần sửa thành:

CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci

Chiến lược phân bảng dữ liệu theo thời gian

Khi bảng chứa quá nhiều dữ liệu, hiệu năng truy vấn giảm đáng kể. Các dữ liệu cũ thường ít được truy xuất. Vì vậy, cần tách dữ liệu ra thành các bảng theo năm.

Quy trình thực hiện:

1. Truy vấn lọc dữ liệu từ năm trước (ví dụ: 2023).
2. Dùng Navicat để xuất kết quả ra file Excel.
3. Tạo bản sao cấu trúc bảng (không có dữ liệu) bằng cách xuất chỉ cấu trúc.
4. Thay đổi tên bảng trong file SQL thành tên_bảng_2023.
5. Chạy file SQL đã sửa vào một cơ sở dữ liệu thử nghiệm để kiểm tra.
6. Nếu ổn, xuất lại bảng từ cơ sở dữ liệu thử nghiệm thành file SQL.
7. Chạy file SQL này vào cơ sở dữ liệu chính.
8. Import lại file Excel vào bảng mới vừa tạo.
9. Sau đó, xóa dữ liệu đã di chuyển khỏi bảng gốc — nhưng hãy sao lưu trước.

Tự động hóa việc phân bảng với Python và Cron

Sử dụng ORM (Flask-SQLAlchemy), ta có thể tạo lớp mô hình động dựa trên tên bảng:

def create_dynamic_model(table_name):
    class DynamicModel(db.Model):
        __tablename__ = table_name
        __table_args__ = (
            db.Index('filtersIndex', 'device_id', 'work_start_time', 'count_detected_pic', 'garbage_type'),
        )
        id = db.Column(db.BigInteger, primary_key=True)
        batch_no = db.Column(db.String(50))
        compress_stall_id = db.Column(db.BigInteger)
        # ... các trường khác
    return DynamicModel

Tạo nhiệm vụ chạy định kỳ mỗi năm để di chuyển dữ liệu:

def move_old_data_to_archive():
    with app.app_context():
        current_year = datetime.now().year
        old_year = current_year - 1
        # Lấy câu lệnh CREATE TABLE hiện tại
        create_sql = db.session.execute('SHOW CREATE TABLE rec_quality_recognition;').fetchone()[1]
        # Thay tên bảng
        new_table_name = f'rec_quality_recognition_{old_year}'
        create_sql = create_sql.replace('rec_quality_recognition', new_table_name)
        # Tạo bảng mới
        db.session.execute(create_sql)
        # Chuyển dữ liệu
        db.session.execute(f'INSERT INTO {new_table_name} SELECT * FROM rec_quality_recognition')
        # Xóa dữ liệu cũ
        db.session.execute('TRUNCATE TABLE rec_quality_recognition;')
        db.session.execute('ALTER TABLE rec_quality_recognition AUTO_INCREMENT = 1;')

# Cài đặt lịch chạy hàng năm vào ngày 1 tháng 1 lúc 0h0m1s
sched = BlockingScheduler()
sched.add_job(move_old_data_to_archive, 'cron', month=1, day=1, hour=0, minute=0, second=1)
sched.start()

Tra cứu dữ liệu theo năm thông minh

Trong ứng dụng, tùy theo năm yêu cầu, chọn đúng bảng để truy vấn:

rec_quality_recognition_year_dict = {}

if 'year' in request.json:
    year = request.json['year']
    current_year = datetime.now().year

    if year < current_year:
        if year in rec_quality_recognition_year_dict:
            model_class = rec_quality_recognition_year_dict[year]
        else:
            model_class = create_dynamic_model(f'rec_quality_recognition_{year}')
            rec_quality_recognition_year_dict[year] = model_class
    else:
        model_class = RecQualityRecognition
else:
    model_class = RecQualityRecognition

# Xây dựng điều kiện truy vấn
filters_list = []
for key, value in request.json.items():
    if key == 'id' and value:
        filters_list.append(model_class.id == value)
    elif key == 'device_id' and value:
        filters_list.append(model_class.device_id == value)

# Thực hiện truy vấn liên kết với nhiều bảng khác
result = db.session.query(model_class, AssetGarbageTruck, ConstDistrict, PlaceBase, ConstGarbageType,
                          AssetQrCamera, NodeObject, NodeLocation, BizOrganization, PlaceCompressStall,
                          PlaceTransferStation, SysServer)\
    .select_from(model_class)\
    .outerjoin(AssetGarbageTruck, model_class.truck_plate_no == AssetGarbageTruck.plate_number)\
    .outerjoin(ConstDistrict, AssetGarbageTruck.work_in_district_id == ConstDistrict.id)\
    # ... tiếp tục các outerjoin
    .filter(*filters_list).one_or_none()