MySQL中 ORDINAL_POSITION的使用小结

-- 创建示例表
create table employees (
    id int primary key,           -- ordinal_position = 1
    first_name varchar(50),       -- ordinal_position = 2
    last_name varchar(50),        -- ordinal_position = 3
    email varchar(100),           -- ordinal_position = 4
    hire_date date               -- ordinal_position = 5
);

-- 查看列顺序
select 
    column_name,
    ordinal_position,
    data_type
from information_schema.columns
where table_schema = database()
  and table_name = 'employees'
order by ordinal_position;

-- 表定义决定了ordinal_position
create table test_order (
    col3 varchar(10),  -- 位置1
    col1 int,          -- 位置2
    col2 date          -- 位置3
);

-- 查询结果
select column_name, ordinal_position
from information_schema.columns
where table_name = 'test_order';
-- 结果：col3(1), col1(2), col2(3)

-- select * 按ordinal_position顺序返回列
select * from employees;
-- 返回顺序：id, first_name, last_name, email, hire_date

-- describe 也按此顺序显示
describe employees;
-- 显示顺序与ordinal_position一致

-- 将email列移到first_name之后
alter table employees
modify column email varchar(100)
after first_name;

-- 查询修改后的顺序
select column_name, ordinal_position
from information_schema.columns
where table_name = 'employees';
-- 新顺序：id(1), first_name(2), email(3), last_name(4), hire_date(5)

-- 重新定义列顺序
alter table employees
    modify id int first,
    modify hire_date date after last_name,
    modify email varchar(100) after first_name;

-- 方法1：通过create table ... as
create table employees_new as
select id, first_name, email, last_name, hire_date
from employees;

drop table employees;
rename table employees_new to employees;

-- 方法2：通过alter table ... rename
alter table employees rename to employees_old;

create table employees (
    -- 按新顺序定义列
    id int primary key,
    first_name varchar(50),
    email varchar(100),
    last_name varchar(50),
    hire_date date
);

insert into employees select * from employees_old;
drop table employees_old;

create table virtual_test (
    id int,
    amount decimal(10,2),
    tax decimal(10,2) generated always as (amount * 0.1) virtual,
    total decimal(10,2) generated always as (amount + tax) virtual
);

-- 虚拟列的ordinal_position
select column_name, ordinal_position, extra
from information_schema.columns
where table_name = 'virtual_test';
-- 结果：id(1), amount(2), tax(3), total(4)

-- 原始表
create table demo (a int, b int, c int);  -- 顺序：a(1), b(2), c(3)

-- 删除中间列
alter table demo drop column b;
-- 新顺序：a(1), c(2)

-- 添加新列
alter table demo add column d int after a;
-- 新顺序：a(1), d(2), c(3)

create table partitioned (
    id int,
    region varchar(20),
    sale_date date,
    amount decimal(10,2)
)
partition by range (year(sale_date)) (
    partition p2023 values less than (2024),
    partition p2024 values less than (2025)
);

-- 分区键不影响ordinal_position
select column_name, ordinal_position
from information_schema.columns
where table_name = 'partitioned';
-- 顺序仍为：id(1), region(2), sale_date(3), amount(4)

-- 生成规范的字段列表
select 
    concat(
        lpad(ordinal_position, 3, '0'), '. ',
        column_name, ' (', 
        column_type, 
        if(is_nullable = 'yes', ', 可空', ', 非空'),
        if(column_key = 'pri', ', 主键', ''),
        ')'
    ) as field_description
from information_schema.columns
where table_schema = database()
  and table_name = 'employees'
order by ordinal_position;

-- 记录列顺序快照
create table column_snapshot (
    snapshot_date date,
    table_name varchar(64),
    column_name varchar(64),
    ordinal_position int,
    data_type varchar(64)
);

-- 插入快照
insert into column_snapshot
select 
    curdate(),
    table_name,
    column_name,
    ordinal_position,
    data_type
from information_schema.columns
where table_schema = database();

-- 检测变化
select 
    cs1.column_name,
    cs1.ordinal_position as old_position,
    cs2.ordinal_position as new_position
from column_snapshot cs1
join information_schema.columns cs2 
    on cs1.table_name = cs2.table_name 
    and cs1.column_name = cs2.column_name
where cs1.snapshot_date = '2024-01-01'
  and cs1.ordinal_position != cs2.ordinal_position;

-- 查看索引中列的顺序（与ordinal_position不同）
select 
    table_name,
    index_name,
    column_name,
    seq_in_index,  -- 在索引中的顺序
    c.ordinal_position  -- 在表中的顺序
from information_schema.statistics s
join information_schema.columns c
    on s.table_schema = c.table_schema
    and s.table_name = c.table_name
    and s.column_name = c.column_name
where s.table_schema = database()
order by table_name, index_name, seq_in_index;

-- 查询列统计信息
select 
    table_name,
    column_name,
    ordinal_position,
    count_star
from performance_schema.table_io_waits_summary_by_table
order by ordinal_position;

-- 应用代码应注意列顺序
-- 不推荐的写法（依赖列顺序）
resultset rs = stmt.executequery("select * from employees");
string firstname = rs.getstring(2);  -- 依赖first_name在第2位

-- 推荐的写法（明确指定列）
resultset rs = stmt.executequery("select first_name, last_name from employees");
string firstname = rs.getstring("first_name");

-- 检查列顺序一致性的脚本
select 
    table_name,
    group_concat(
        concat(ordinal_position, '.', column_name) 
        order by ordinal_position
    ) as column_sequence
from information_schema.columns
where table_schema = database()
  and table_name in ('table1', 'table2', 'table3')
group by table_name
order by table_name;