首页 » MySQL/TiDB/GoldenDB, ORACLE 9i-23ai, PostgreSQL/GaussDB » Oracle、MySQL、PostGreSQL、SQL Server数据库比较系列（三）: VARCHAR与VARCHAR2
Oracle、MySQL、PostGreSQL、SQL Server数据库比较系列（三）: VARCHAR与VARCHAR2
Oracle 数据库中的 varchar 和 varchar2 数据类型都用于存储字母数字值的动态长度，也是最常用的字段数据类型。它们之间存在一些差异。varchar 数据类型是适用于所有关系数据库产品（Oracle、MySQL、PostgreSQL 和等）的 ANSI 标准数据类型，存储长度不同数据库差异较大。而 varchar2 数据类型是 Oracle 标准数据类型，仅适用于 Oracle 数据库，它最多可存储 4000 个字节, 12c后新特性已经4,000增长32,767 bytes.Oracle 7.0 引入了 varchar2 数据类型，Varchar2 不区分 NULL 和空字符串，而且永远不会。如果你依赖空字符串和 NULL 是同一个东西，你应该使用 varchar2。VARCHAR是Varchar2的同义词，当我们创建 varchar 数据类型时，Oracle 服务器会在内部自动将 varchar 数据类型转换为 varchar2 数据类型。而部分国产库是VARCHAR2是VARCHAR的同义词。
可见Oracle已经用varchar2替换了varchar， 那在国产化数据库推进过程中，大量应用从Oracle迁移，希望可以兼容oracle， 挑了2个测试一下效果怎么样。
长度上限
-- PostgreSQL
weejar=# create table test(id int,name varchar(65536000));
错误: 类型 varchar 的长度不能超过 10485760
第1行create table test(id int,name varchar(65536000));
-- MySQL
mysql> create table test1321(id int,name varchar(80000));
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead
-- Oracle 19.3
SQL> create table test1341(id int,name varchar(32769 byte));
create table test1341(id int,name varchar(32769 byte))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL> ho oerr ora 910
00910, 00000, "specified length too long for its datatype"
// *Cause: for datatypes CHAR and RAW, the length specified was > 2000;
// otherwise, the length specified was > 4000.
// *Action: use a shorter length or switch to a datatype permitting a
// longer length such as a VARCHAR2, LONG CHAR, or LONG RAW
Note:
当然长度不是判断数据库好坏的标准，会有其它类型补充。
# ORACLE
SQL> create table test(id int,name varchar2(20)); Table created. SQL> desc test Name Null? Type ------------------------------- -------- ---------------------------- 1 ID NUMBER(38) 2 NAME VARCHAR2(20) SQL> create table test1(id int,name varchar(20)); Table created. SQL> desc test1 Name Null? Type ------------------------------- -------- ---------------------------- 1 ID NUMBER(38) 2 NAME VARCHAR2(20) SQL> insert into test1 values(1,null); 1 row created. SQL> select * from test1 where name=''; no rows selected SQL> insert into test1 values(2,''); 1 row created. SQL> select * from test1 where name is null; ID NAME ---------- -------------------- 1 2
Note:
创建时使用的varchar，oracle也自动转换为varchar2.null和”是相同，使用is null匹配。
PostGreSQL
C:\Users\zhang>psql psql (13.1) 输入 "help" 来获取帮助信息. weejar=# create table test(id int,name varchar2(20)); 错误: 类型 "varchar2" 不存在 第1行create table test(id int,name varchar2(20)); weejar=# create table test(id int,name varchar(20)); CREATE TABLE weejar=# insert into test values(1,null); INSERT 0 1 weejar=# insert into test values(2,''); INSERT 0 1 weejar=# select * from test where name is null; id | name ----+------ 1 | (1 行记录) weejar=# select * from test where name =''; id | name ----+------ 2 | (1 行记录)
MySQL
[root@oel7db1 ~]# mysql -uroot -pwww.anbob.com -hlocalhost mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.20-commercial MySQL Enterprise Server - Commercial Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use anbob Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table test(id int,name varchar2(20)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar2(20))' at line 1 mysql> create table test(id int,name varchar(20)); Query OK, 0 rows affected (0.12 sec) mysql> desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into test values(1,null); Query OK, 1 row affected (0.07 sec) mysql> insert into test values(2,''); Query OK, 1 row affected (0.01 sec) mysql> select * from test where name is null; +------+------+ | id | name | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.00 sec) mysql> select * from test where name=''; +------+------+ | id | name | +------+------+ | 2 | | +------+------+ 1 row in set (0.00 sec)
OpenGauss
[og@oel7db1 ~]$ sh pg_connect.sh
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr )
NOTICE : The password has been expired, please change the password.
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
anbob=# create table test(id int,name varchar2(20));
CREATE TABLE
anbob=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
anbob=# insert into test values(1,null);
INSERT 0 1
anbob=# insert into test values(2,'');
INSERT 0 1
anbob=# select * from test where name is null;
id | name
----+------
1 |
2 |
(2 rows)
anbob=# select * from test where name='';
id | name
----+------
(0 rows)
Note:
注意opengauss是为varchar2创建了同义词到varchar.
Yugabyte DB
[root@oel7db1 yugabyte-2.11.1.0]# bin/ysqlsh -U yugabyte -d yugabyte
ysqlsh (11.2-YB-2.11.1.0-b0)
Type "help" for help.
yugabyte=# create table test(id int,name varchar2(20));
ERROR: type "varchar2" does not exist
LINE 1: create table test(id int,name varchar2(20));
^
yugabyte=# create table test(id int,name varchar(20));
CREATE TABLE
yugabyte=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
Yugabyte DB常和TIDB对标，也是在快速崛起，不同的是它是PostGreSQL兼容，而TIDB是MySQL兼容, 和PG一样未提供varchar2. Yugabyte的安装是体验很好。
万里开源GreatDB
[greatdb@sqlnode1 ~]$ greatsql -P 3306 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4274225 Server version: 8.0.25-15-greatdbcluster5.0.8-rc GreatDB Cluster, Release rc, Revision 58f870ab228 Copyright (c) 2009-2021 BEIJING GREAT OPENSOURCE SOFTWARE TECHNOLOGY CO.,LTD. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. GreatDB Cluster[(none)]> use anbob GreatDB Cluster[anbob]> create table test(id int,name varchar2(20)); Query OK, 0 rows affected (0.04 sec) GreatDB Cluster[anbob]> desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) GreatDB Cluster[anbob]> insert into test values(1,null); Query OK, 1 row affected (0.00 sec) GreatDB Cluster[anbob]> insert into test values(2,''); Query OK, 1 row affected (0.00 sec) GreatDB Cluster[anbob]> select * from test where name is null; +------+------+ | id | name | +------+------+ | 1 | NULL | +------+------+ 1 row in set (0.01 sec) GreatDB Cluster[anbob]> select * from test where name=''; +------+------+ | id | name | +------+------+ | 2 | | +------+------+ 1 row in set (0.00 sec)
Note:
万里开源是MySQL系的国产化数据库，有适配VARCHAR2，和OpenGuass一样是varchar2是varchar的同义词, 和Oracle正好相反。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。