更新时间:2022-08-16 GMT+08:00

事务管理与数据库管理

本节介绍如何迁移MySQL事务及数据库管理方面的关键字和功能。

事务管理

  1. TRANSACTION

    DSC工具在迁移MySQL事务处理语句时会根据GaussDB特性进行相应适配。

    输入示例

    ##该声明仅适用于会话中执行的下一个单个事务
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET TRANSACTION READ ONLY;
    SET TRANSACTION READ WRITE;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED,READ ONLY;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,READ WRITE;
    ##使用SESSION关键字,适用于当前会话中执行的所有后续事务
    START TRANSACTION;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    commit ;

    输出示例

    --该声明仅适用于会话中执行的下一个单个事务
    SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET LOCAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SET LOCAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET LOCAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET LOCAL TRANSACTION READ ONLY;
    SET LOCAL TRANSACTION READ WRITE;
    SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
    SET LOCAL TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;
    --使用SESSION关键字,适用于当前会话中执行的所有后续事务
    START TRANSACTION;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    COMMIT WORK;

  2. LOCK

    DSC工具在迁移MySQL 事务处理锁表语句时会根据GaussDB特性进行相应适配。

    输入示例

    ## A.
    START TRANSACTION;
    LOCK TABLES `mt`.`runoob_tbl` WRITE,`mt`.`runoob_tb2` READ;
    commit;
    
    ## B.
    START TRANSACTION;
    LOCK TABLES `mt`.`runoob_tbl` WRITE;
    commit;
    
    ## C.
    START TRANSACTION;
    LOCK TABLES `mt`.`runoob_tbl` READ,`mt`.`runoob_tbl` AS t1 READ;
    commit;

    输出示例

    -- A.
    START TRANSACTION;
    LOCK TABLE "mt"."runoob_tbl" IN ACCESS EXCLUSIVE MODE;
    LOCK TABLE "mt"."runoob_tb2" IN ACCESS SHARE MODE;
    COMMIT WORK;
    
    -- B.
    START TRANSACTION;
    LOCK TABLE "mt"."runoob_tbl" IN ACCESS EXCLUSIVE MODE;
    COMMIT WORK;
    
    -- C.
    START TRANSACTION;
    LOCK TABLE "mt"."runoob_tbl" IN ACCESS SHARE MODE;
    COMMIT WORK;

数据库管理

  1. SET CHARACTER

    DSC工具迁移时会将MySQL "SET CHARACTER SET"语句迁移为"SET SESSION NAMES"。字符集对照如下表。

    表1

    MySQL CHARACTER SET

    GaussDB SESSION NAMES

    ASCII

    SQL_ASCII

    BIG5

    BIG5

    CP1250

    WIN1250

    CP1251

    WIN1251

    CP1256

    WIN1256

    CP1257

    WIN1257

    CP932

    SJIS

    EUCJPMS

    EUC_JP

    EUCKR

    EUC_KR

    GB2312

    GB18030

    GBK

    GBK

    GREEK

    ISO_8859_7

    HEBREW

    ISO_8859_8

    KOI8R

    KOI8R

    KOI8U

    KOI8U

    LATIN1

    LATIN1

    LATIN2

    LATIN2

    LATIN5

    LATIN5

    LATIN7

    LATIN7

    SJIS

    SJIS

    SWE7

    UTF8

    TIS620

    WIN874

    UTF8

    UTF8

    UTF8MB4

    UTF8

    输入示例

    SET CHARACTER SET 'ASCII';
    SET CHARACTER SET 'BIG5';
    SET CHARACTER SET 'CP1250';
    SET CHARACTER SET 'CP1251';
    SET CHARACTER SET 'CP1256';
    SET CHARACTER SET 'CP1257';
    SET CHARACTER SET 'CP932';
    SET CHARACTER SET 'EUCJPMS';
    SET CHARACTER SET 'EUCKR';
    SET CHARACTER SET 'GB2312';
    SET CHARACTER SET 'GBK';
    SET CHARACTER SET 'GREEK';
    SET CHARACTER SET 'HEBREW';
    SET CHARACTER SET 'KOI8R';
    SET CHARACTER SET 'KOI8U';
    SET CHARACTER SET 'LATIN1';
    SET CHARACTER SET 'LATIN2';
    SET CHARACTER SET 'LATIN5';
    SET CHARACTER SET 'LATIN7';
    SET CHARACTER SET 'SJIS';
    SET CHARACTER SET 'SWE7';
    SET CHARACTER SET 'TIS620';
    SET CHARACTER SET 'UTF8';
    SET CHARACTER SET 'UTF8MB4';
    ##mysql中不支持 SET CHARACTER SET 'UCS2';
    ##mysql中不支持SET CHARACTER SET 'UTF16';
    ##mysql中不支持SET CHARACTER SET 'UTF16LE';
    ##mysql中不支持SET CHARACTER SET 'UTF32';

    输出示例

    SET SESSION NAMES 'SQL_ASCII';
    SET SESSION NAMES 'BIG5';
    SET SESSION NAMES 'WIN1250';
    SET SESSION NAMES 'WIN1251';
    SET SESSION NAMES 'WIN1256';
    SET SESSION NAMES 'WIN1257';
    SET SESSION NAMES 'SJIS';
    SET SESSION NAMES 'EUC_JP';
    SET SESSION NAMES 'EUC_KR';
    SET SESSION NAMES 'GB18030';
    SET SESSION NAMES 'GBK';
    SET SESSION NAMES 'ISO_8859_7';
    SET SESSION NAMES 'ISO_8859_8';
    SET SESSION NAMES 'KOI8R';
    SET SESSION NAMES 'KOI8U';
    SET SESSION NAMES 'LATIN1';
    SET SESSION NAMES 'LATIN2';
    SET SESSION NAMES 'LATIN5';
    SET SESSION NAMES 'LATIN7';
    SET SESSION NAMES 'SJIS';
    SET SESSION NAMES 'UTF8';
    SET SESSION NAMES 'WIN874';
    SET SESSION NAMES 'UTF8';
    SET SESSION NAMES 'UTF8';
    --mysql中不支持 SET CHARACTER SET 'UCS2';
    --mysql中不支持SET CHARACTER SET 'UTF16';
    --mysql中不支持SET CHARACTER SET 'UTF16LE';
    --mysql中不支持SET CHARACTER SET 'UTF32';