不同数据库操作对比

发表于 分类于 阅读次数： 阅读次数： Changyan： 本文字数： 2.5k 阅读时长 ≈ 2 分钟
请添加个描述...

不同数据库操作

可重复执行 删除列

1
2
3
4
5
6
7
8
达梦，海量vast:
DECLARE N NUMBER;
BEGIN
  SELECT COUNT(1) INTO N FROM USER_TAB_COLUMNS WHERE UPPER(TABLE_NAME)=UPPER('xx_tablename') AND UPPER(COLUMN_NAME)=UPPER('xx_columnname');
  IF N > 0 THEN
       EXECUTE IMMEDIATE 'ALTER TABLE xx_tablename DROP COLUMN xx_columnname';
  END IF;
END;
1
2
3
4
5
6
7
8
9
10
11
mysql:
DELIMITER $$
CREATE PROCEDURE schema_change()
BEGIN
    IF EXISTS (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='xx_tablename' AND COLUMN_NAME='xx_columnname') THEN
        ALTER TABLE xx_tablename DROP COLUMN xx_columnname;
    END IF;
END $$
DELIMITER ;
CALL schema_change();
DROP PROCEDURE IF EXISTS schema_change;
1
2
金仓，瀚高：
ALTER TABLE xx_tablename DROP COLUMN IF EXISTS xx_columnname;

可重复执行 修改列

1
2
3
达梦，海量mysql
ALTER TABLE tableName MODIFY columnName VARCHAR(300);
ALTER TABLE B01 MODIFY DELETEFLAG TINYINT default 0;
1
2
金仓，瀚高：
ALTER TABLE tableName ALTER COLUMN columnName TYPE VARCHAR(300);

新增列

1
2
3
4
5
6
7
8
9
达梦，海量
DECLARE N NUMBER;
BEGIN
SELECT COUNT(1) INTO N FROM USER_TAB_COLUMNS WHERE UPPER(TABLE_NAME)=UPPER('xx_tablename') AND UPPER(COLUMN_NAME)=UPPER('xx_columnname');
IF N = 0 THEN
       EXECUTE IMMEDIATE 'ALTER TABLE xx_tablename add xx_columnname VARCHAR(1)';
END IF;
END;
COMMENT ON COLUMN xx_tablename.xx_columnname IS 'xxxxxxxxxx';
1
2
3
4
5
6
7
8
9
10
11
12
mysql

DELIMITER $$
CREATE PROCEDURE schema_change()
BEGIN
    IF not EXISTS (SELECT * FROM information_schema.columns WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='xx_tablename' AND COLUMN_NAME='xx_columnname') THEN
ALTER TABLE xx_tablename add COLUMN xx_columnname VARCHAR(1) COMMENT  'xxxxxxxxx';
END IF;
END $$
DELIMITER ;
CALL schema_change();
DROP PROCEDURE IF EXISTS schema_change;
1
2
3
4
金仓，瀚高：
ALTER TABLE tableName ADD COLUMN IF NOT EXISTS columnName VARCHAR(1);
COMMENT ON COLUMN tableName.columnName IS 'xxxxxxxxxx';

注释

1
2
3
达梦，金仓，瀚高
comment on table tableName is 'xxxxxxxx';
COMMENT ON COLUMN tableName.columnName IS 'xxxxxxxx';
1
2
3
mysql
alter table tableName COMMENT 'xxxxxxxxx';
alter table tableName modify column columnName varchar(100) COMMENT 'xxxxxxxxxxxxx';

建表

1
2
3
4
5
6
7
8
达梦，金仓，瀚高
DROP TABLE IF EXISTS TABLE_NAME_1;
CREATE TABLE TABLE_NAME_1 (
	RECORDID VARCHAR2(40) NOT NULL,
  createuser VARCHAR2(40) NULL,
  createtime TIMESTAMP(6) NULL
);
select  * from TABLE_NAME_1;
1
2
3
4
5
6
7
8
mysql
CREATE TABLE [IF NOT EXISTS] table_name(

column1 datatype ,

column2 datatype ,

);

表重命名

1
2
3
4
5
6
7
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM USER_TABLES WHERE TABLE_NAME = 'SynConfigSheetKey'
  ) THEN
    EXECUTE IMMEDIATE 'ALTER TABLE SynConfigSheetKey RENAME TO SYN_CONFIG_SHEET_KEY';
  END IF;
END;

表备份

1
CREATE TABLE JYPX02_bak_20241214 AS SELECT * FROM JYPX02;