添加字段

DROP PROCEDURE IF EXISTS add_column_if_not_exists;
DELIMITER //
CREATE PROCEDURE add_column_if_not_exists()
BEGIN
    IF NOT EXISTS (
        SELECT * FROM information_schema.columns 
            WHERE table_schema = $dbname 
                AND table_name = $tablename 
            AND column_name = $column
                ) THEN
                    ALTER TABLE $tablename
                    ADD COLUMN $column $type;
                END IF;
            END //
            DELIMITER ;
            CALL add_column_if_not_exists()

查询

// 查询major表数据，根据category分组，每个不同的category值只查询 3 条数据
$sql = "SELECT id,name FROM 
            (SELECT id,name, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY id DESC) AS row_num 
                FROM major 
                WHERE category IN ($ids) 
            ) AS res 
        WHERE row_num <= 3";
$data = $this->db->query($sql);