备案控制台
探索云世界
开发者社区 数据库 文章 正文

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之四：特殊字符和符号

2016-08-04 4206
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
简介：

PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。

空字符串( '' )

Oracle中，空字符串( '' )很多时候是和null同样处理的。给varchar2和char类型赋值的时候按照null处理。在给日期类型或者数字类型赋值的时候，也是按照null处理。但是在where条件部分，=‘’和 is null 是不同的。

PostgreSQL里面，空字符串( '' )和 null是不同的。完全是不同的处理。转换SQL的时候，一定要注意。

Oracle 空字符串
SQL> create table o_test(value1 number, value2 varchar2(10), value3 date);

表已创建。

SQL> insert into o_test values('', '11111', to_date( '2010-01-01','YYYY-MM-DD'));

已创建 1 行。

SQL> insert into o_test values(1, '', to_date( '2010-01-01','YYYY-MM-DD'));

已创建 1 行。

SQL> insert into o_test values(2, '22222', to_date( '','YYYY-MM-DD'));

已创建 1 行。

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           11111      01-1月 -10
         1            01-1月 -10
         2 22222

SQL> select * from o_test where value1 = '';

未选定行

SQL> select * from o_test where value1 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           11111      01-1月 -10

SQL> select * from o_test where value2 = '';

未选定行

SQL> select * from o_test where value2 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1            01-1月 -10

SQL> select * from o_test where value3 = '';

未选定行

SQL> select * from o_test where value3 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         2 22222
PostgreSQL 空字符串
postgres=# create table p_test(value1 integer, value2 varchar(10), value3 timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into p_test values('', '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
错误:  无效的整数类型输入语法: ""
第1行insert into p_test values('', '11111', to_timestamp('2010-01...
                               ^
postgres=#  insert into p_test values(null, '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(1, '', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(2, '22222', to_timestamp('', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# select * from p_test;
 value1 | value2 |         value3
--------+--------+------------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2010-01-01 00:00:00
      2 | 22222  | 0001-01-01 00:00:00 BC
(3 行记录)

postgres=# select * from p_test where value1 = '';
错误:  无效的整数类型输入语法: ""
第1行select * from p_test where value1 = '';
                                         ^

postgres=# select * from p_test where value1 is null;
 value1 | value2 |       value3
--------+--------+---------------------
        | 11111  | 2010-01-01 00:00:00
(1 行记录)

postgres=# select * from p_test where value2 =  '';
 value1 | value2 |       value3
--------+--------+---------------------
      1 |        | 2010-01-01 00:00:00
(1 行记录)

postgres=# select * from p_test where value2 is null;
 value1 | value2 | value3
--------+--------+--------
(0 行记录)

postgres=# select * from p_test where value3 is null;
 value1 | value2 | value3
--------+--------+--------
(0 行记录)

postgres=# select * from p_test where to_char(value3, 'YYYY-MM-DD') ='0001-01-01';
 value1 | value2 |         value3
--------+--------+------------------------
      2 | 22222  | 0001-01-01 00:00:00 BC
(1 行记录)

比较运算符

Oracle中，比较运算符之间是可以有空格的。比如【> = 】这样的写法是允许的。而PostgreSQL中，运算符之间不能有空格。

Oracle 比较运算符
SQL> select * from o_test where value1 > = 2;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         2 22222

SQL> select * from o_test where value1 <                         = 3;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1            01-1月 -10
         2 22222
PostgreSQL 比较运算符
postgres=# select * from p_test where value1 > = 2;
错误:  语法错误 在 "=" 或附近的
第1行select * from p_test where value1 > = 2;
                                         ^
postgres=# select * from p_test where value1 >= 2;
 value1 | value2 |         value3
--------+--------+------------------------
      2 | 22222  | 0001-01-01 00:00:00 BC
(1 行记录)

postgres=# select * from p_test where value1 < = 3;
错误:  语法错误 在 "=" 或附近的
第1行select * from p_test where value1 < = 3;
                                         ^
postgres=# select * from p_test where value1 <= 3;
 value1 | value2 |         value3
--------+--------+------------------------
      1 |        | 2010-01-01 00:00:00
      2 | 22222  | 0001-01-01 00:00:00 BC
(2 行记录)

逃逸字符

逃逸的意思是有特殊意义的字符，前面如果加上逃逸字符的话，就不代表它的特殊含义，而代表它的字符本意。
Oracle的SQL中，使用( ' )来逃逸它本身。Oracle没有其它的标准逃逸字符。比如"n"并不代表回车，而是代表它的本意的"n"两个字符。如果输入回车的话，使用chr(10)来表示。在正则表达式等需要使用特殊字符本意的时候，使用关键字(escape)后面定义的字符进行逃逸。

PostgreSQL的SQL，也支持使用( ' )来逃逸它本身。老版的还支持使用反斜杠( )， 但新版已经不使用。Oracle定义了标准的逃逸字串（E'XXX'） 格式。 在正则表达式等需要使用特殊字符本意的时候，默认使用""做逃逸字串。也可以使用关键字(escape)后面定义的字符进行逃逸。

数据迁移的时候，老版的PostgreSQL中，需要对逃逸字串进行特殊处理。比如把"“替换成"\\"。新版已经不再需要。可以直接按照Oracle的方式直接转换。

Oracle 逃逸字符
SQL> insert into o_test values(1, '12e34'6r8', null);
ERROR:
ORA-01756: 引号内的字符串没有正确结束

SQL> insert into o_test values(1, '12e34''6r8', null);

已创建 1 行。

SQL> insert into o_test values(1, '12e34\n6r8', null);

已创建 1 行。

SQL> insert into o_test values(1, '12e34' || chr(10) || '6r8', null);

已创建 1 行。

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678
         1 12e34%6r8
         1 12e34'6r8
         1 12e34\n6r8
         1 12e34
           6r8

SQL> select * from o_test where value2 like '%_%';

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678
         1 12e34%6r8
         1 12e34'6r8
         1 12e34\n6r8
         1 12e34
           6r8

SQL> select * from o_test where value2 like '%\_%';

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 12e34\n6r8

SQL> select * from o_test where value2 like '%\_%' escape '\' ;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678

SQL> select * from o_test where value2 like '%r_%' escape 'r' ;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678
PostgreSQL 逃逸字符
postgres=#  insert into p_test values(1, '12e34'6r8', null);
postgres'# ');
错误:  语法错误 在 "6" 或附近的
第1行insert into p_test values(1, '12e34'6r8', null);
                                         ^
postgres=#  insert into p_test values(1, '12e34''6r8', null);
INSERT 0 1
postgres=#  insert into p_test values(1, '\n\r', null);
INSERT 0 1
postgres=# insert into p_test values(1, E'ab\n\rc', null);
INSERT 0 1
postgres=# insert into p_test values(1, 'ab'|| chr(10) || 'c', null);
INSERT 0 1
postgres=# select * from p_test;
 value1 |  value2   | value3
--------+-----------+--------
      1 | 1234_678  |
      1 | 12e34%6r8 |
      1 | 12e34'6r8 |
      1 | \n\r      |
      1 | ab       +|
        | \rc       |
      1 | ab       +|
        | c         |
(6 行记录)

postgres=# select * from p_test where value2 like '%_%';
 value1 |  value2   | value3
--------+-----------+--------
      1 | 1234_678  |
      1 | 12e34%6r8 |
      1 | 12e34'6r8 |
      1 | \n\r      |
      1 | ab       +|
        | \rc       |
      1 | ab       +|
        | c         |
(6 行记录)

postgres=# select * from p_test where value2 like '%\_%';
 value1 |  value2  | value3
--------+----------+--------
      1 | 1234_678 |
(1 行记录)

postgres=# select * from p_test where value2 like '%r_%' escape 'r' ;
 value1 |  value2  | value3
--------+----------+--------
      1 | 1234_678 |
(1 行记录)

postgres=# select * from p_test where value2 like '%\\%';
 value1 | value2 | value3
--------+--------+--------
      1 | \n\r   |
(1 行记录)
文章标签：
云原生数据库 PolarDB
关系型数据库
SQL
PostgreSQL
Oracle
数据库
存储
开发者
关键词：
SQL分析
云原生数据库 PolarDB sql
云原生数据库 PolarDB oracle
SQL笔记
SQL Oracle
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
瀚高大李
目录
相关文章
LightOf
|
16天前
|
关系型数据库 MySQL
【MySQL实战笔记】07 | 行锁功过：怎么减少行锁对性能的影响？-01
【4月更文挑战第18天】MySQL的InnoDB引擎支持行锁，而MyISAM只支持表锁。行锁在事务开始时添加，事务结束时释放，遵循两阶段锁协议。为减少锁冲突影响并发，应将可能导致最大冲突的锁操作放在事务最后。例如，在电影票交易中，应将更新影院账户余额的操作安排在事务末尾，以缩短锁住关键行的时间，提高系统并发性能。
LightOf
14 4
LightOf
|
17天前
|
关系型数据库 MySQL 数据库
【MySQL实战笔记】 06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？-01
【4月更文挑战第17天】MySQL的锁分为全局锁、表级锁和行锁。全局锁用于全库备份，可能导致业务暂停或主从延迟。不加锁备份会导致逻辑不一致。推荐使用`FTWRL`而非`readonly=true`因后者可能影响其他逻辑且异常处理不同。表级锁如`lock tables`限制读写并限定操作对象，常用于并发控制。元数据锁（MDL）在访问表时自动加锁，确保读写正确性。
LightOf
66 31
Byyyi
|
1天前
|
存储 SQL 关系型数据库
MySQL万字超详细笔记❗❗❗
MySQL万字超详细笔记❗❗❗
Byyyi
32 1
MySQL万字超详细笔记❗❗❗
辰辰zZ
|
6天前
|
SQL 关系型数据库 MySQL
【MySQL系列笔记】MySQL总结
MySQL 是一种关系型数据库，说到关系，那么就离不开表与表之间的关系，而最能体现这种关系的其实就是我们接下来需要介绍的主角 SQL，SQL 的全称是 Structure Query Language ，结构化的查询语言，它是一种针对表关联关系所设计的一门语言，也就是说，学好 MySQL，SQL 是基础和重中之重。SQL 不只是 MySQL 中特有的一门语言，大多数关系型数据库都支持这门语言。
辰辰zZ
39 8
辰辰zZ
|
6天前
|
SQL 关系型数据库 MySQL
【MySQL系列笔记】常用SQL
常用SQL分为三种类型，分别为DDL，DML和DQL；这三种类型的SQL语句分别用于管理数据库结构、操作数据、以及查询数据，是数据库操作中最常用的语句类型。 在后面学习的多表联查中，SQL是分析业务后业务后能否实现的基础，以及后面如何书写动态SQL，以及完成级联查询的关键。
辰辰zZ
20 6
辰辰zZ
|
6天前
|
存储 关系型数据库 MySQL
【MySQL系列笔记】InnoDB引擎-数据存储结构
InnoDB 存储引擎是MySQL的默认存储引擎，是事务安全的MySQL存储引擎。该存储引擎是第一个完整ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和 CPU。因此很有必要学习下InnoDB存储引擎，它的很多架构设计思路都可以应用到我们的应用系统设计中。
辰辰zZ
27 4
辰辰zZ
|
6天前
|
SQL 存储 关系型数据库
【MySQL系列笔记】SQL优化
SQL优化是通过调整数据库查询、索引、表结构和配置参数等方式，提高SQL查询性能和效率的过程。它旨在减少查询执行时间、减少系统资源消耗，从而提升数据库系统整体性能。优化方法包括索引优化、查询重写、表分区、适当选择和调整数据库引擎等。
辰辰zZ
28 3
讓丄帝愛伱
|
7天前
|
SQL 关系型数据库 数据库
SQL 42501: Postgresql查询中的权限不足错误
SQL 42501: Postgresql查询中的权限不足错误
讓丄帝愛伱
20 0
屿小夏
|
8天前
|
SQL 关系型数据库 MySQL
【MySQL】：探秘主流关系型数据库管理系统及SQL语言
【MySQL】：探秘主流关系型数据库管理系统及SQL语言
屿小夏
15 0
请回答1024
|
13天前
|
SQL 存储 Oracle
《SQL必知必会》个人笔记
《SQL必知必会》个人笔记
请回答1024
16 1

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 3
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 4
    数据库管理与电脑监控软件：SQL代码优化与实践
  • 5
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 6
    SQL常见面试题总结2
  • 7
    大模型与数据分析：探索Text-to-SQL（上）
  • 8
    NL2SQL进阶系列(4)：ConvAI、DIN-SQL等16个业界开源应用实践详解[Text2SQL]
  • 9
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 10
    大模型与数据分析：探索Text-to-SQL（中）
  • 1
    连接和管理RDS
    30
  • 2
    购买和初始化阿里云RDS
    29
  • 3
    rds教程
    28
  • 4
    TiDB与MySQL、PostgreSQL等数据库的比较分析
    150
  • 5
    OBCP实践 - 迁移 MySQL 数据到 OceanBase 集群
    47
  • 6
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    44
  • 7
    TIDB和MySQL的区别
    194
  • 8
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    69
  • 9
    [MySQL]事务原理之redo log,undo log
    66
  • 10
    [MySQL]SQL优化之索引的使用规则
    39

    • 相关课程

    更多
  • MySQL高级应用 - 索引和锁
  • Linux MySQL服务器搭建与应用
  • PostgreSQL云上开发实践
  • MySQL完全自学手册
  • MySQL数据库入门学习
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • 云栖大会：开源 PolarDB 架构演进、关键技术与社区建设
  • 2023云栖大会：和客户一起玩转PolarDB新特性
  • 2023云栖大会：PolarDB for AI

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • 阿里云百炼xAnalyticDB PostgreSQL构建AIGC应用
  • 云原生数据库PolarDB快速入门
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 使用CloudLens采集PolarDB日志并进行审计分析
  • PolarDB for PostgreSQL 闪回特性体验

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • oracle
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）