作者
digoal
日期
2020-10-31
标签
PostgreSQL , MySQL
背景
https://www.runoob.com/mysql/mysql-alter.html
如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;
FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
修改存储引擎：修改为myisam
alter table tableName engine=myisam;
删除外键约束：keyName是外键别名
alter table tableName drop foreign key keyName;
修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面
alter table tableName modify name1 type1 first|after name2;
正常情况下, 如果修改列的顺序或者加列指定顺序, 会涉及表的存储重写, 应该是很慢的操作(取决于表里的数据多少).
原因是解析行的内容时, 存储结构必须匹配元数据结构(字段类型定义和顺序).
不管MySQL是怎么实现的, 个人认为修改位置一定会导致rewrite table.
PostgreSQL加列实际上就是改元数据定义, 不涉及query rewrite. 所以不支持按指定位置加列, 或者修改列的位置. 那么如果业务就是有这样的需求, 怎么实现呢?
可以用simple view, 因为PG的view和table一样, 支持dml(通过query rewrite rule实现).
例子:
```
postgres=> create table t_orig (c1 int , c2 int, c3 int);
CREATE TABLE
Time: 4.843 ms
postgres=> create view v_orig as select c1,c3,c2 from t_orig;
CREATE VIEW
Time: 4.041 ms
postgres=> insert into v_orig values (1,2,3);
INSERT 0 1
Time: 2.325 ms
postgres=> select * from t_orig;
c1 | c2 | c3
----+----+----
1 | 3 | 2
(1 row)
Time: 2.168 ms
postgres=> select * from v_orig;
c1 | c3 | c2
----+----+----
1 | 2 | 3
(1 row)
Time: 2.168 ms
postgres=> alter table t_orig add column c4 int;
ALTER TABLE
Time: 3.857 ms
postgres=> begin;
BEGIN
Time: 1.970 ms
postgres=> drop view v_orig;
DROP VIEW
Time: 3.544 ms
postgres=> create or replace view v_orig as select c1,c4,c3,c2 from t_orig;
CREATE VIEW
Time: 4.815 ms
postgres=> end;
COMMIT
Time: 2.005 ms
postgres=> select * from v_orig;
c1 | c4 | c3 | c2
----+----+----+----
1 | | 2 | 3
(1 row)
Time: 2.247 ms
postgres=> explain delete from v_orig where c1=1;
QUERY PLAN
Delete on t_orig (cost=0.00..33.12 rows=9 width=6)
-> Seq Scan on t_orig (cost=0.00..33.12 rows=9 width=6)
Filter: (c1 = 1)
(3 rows)
Time: 2.176 ms
postgres=> delete from v_orig where c1=1;
DELETE 1
Time: 2.159 ms
```
PG 使用query write来实现按位置加列, 修改列位置效果, 不需要rewrite table, 只需要修改view定义.
读写需通过view来实现.
当然了, 一般来讲业务很少有指定位置加列需求.
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
