CREATE TABLE example (
    id CHAR(10) NOT NULL,
    name VARCHAR(100),
    -- 其他字段...
    PRIMARY KEY (id)
);

-- 插入数据时手动处理自增逻辑
INSERT INTO example (id, name) 
VALUES (CONCAT('ID_', LPAD((SELECT COALESCE(MAX(CAST(SUBSTRING(id, 4) AS UNSIGNED)), 0) + 1 FROM example), 6, '0')), 'John');

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    -- 其他字段...
);

-- 查询时转换为 CHAR 格式
SELECT CONCAT('ID_', LPAD(id, 6, '0')) AS formatted_id, name 
FROM example;

CREATE TABLE example (
    id CHAR(10) NOT NULL PRIMARY KEY,
    name VARCHAR(100)
);

DELIMITER //
CREATE TRIGGER before_insert_example
BEFORE INSERT ON example
FOR EACH ROW
BEGIN
    DECLARE next_id INT;
    SET next_id = IFNULL((SELECT CAST(SUBSTRING(MAX(id), 4) AS UNSIGNED) 
                          FROM example 
                          WHERE id LIKE 'ID_%'), 0) + 1;
    SET NEW.id = CONCAT('ID_', LPAD(next_id, 6, '0'));
END//
DELIMITER ;