varchar和nvarchar类型

oracle 

SQL> select userenv('language') from dual;
 
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8
 
SQL> 
SQL> create table tmp1 (name1 varchar2(9),name2 varchar2(9),name3 nvarchar2(9),name4 nvarchar2(9));
 
Table created
SQL> insert into tmp1 (NAME1, NAME2, NAME3, NAME4) values ('123456789', '中国中', '123456789', '中国中中国中中国中');
 
1 row inserted
SQL> select * from tmp1;
 
NAME1     NAME2     NAME3               NAME4
--------- --------- ------------------- -------------------
123456789 中国中    123456789           中国中中国中中国中
SQL> select length(name1),length(name2),length(name3),length(name4) from tmp1 t;
 
LENGTH(NAME1) LENGTH(NAME2) LENGTH(NAME3) LENGTH(NAME4)
------------- ------------- ------------- -------------
            9             3             9             9
SQL> select lengthb(name1),lengthb(name2),lengthb(name3),lengthb(name4) from tmp1 t
 
  2  /
 
LENGTHB(NAME1) LENGTHB(NAME2) LENGTHB(NAME3) LENGTHB(NAME4)
-------------- -------------- -------------- --------------
             9              9             18             18
 

说明：
lengthb=vsize 返回字节数
length=lengthc 字符数
-- 在utf-8的字符集下中文占三个字节，gbk2312 占2个字节
可以使用varchar2(9 byte)或者varchar2(9 char)来创建，默认是varchar2(9 byte)，而nvarchar2(9)没有这种方法，并且默认创建的是字符大小

Sqlserver

察看字符集
SELECT COLLATIONPROPERTY('Chinese_PRC_Stroke_CI_AI_KS_WS', 'CodePage')
/*936 简体中文GBK
950 繁体中文BIG5
437 美国/加拿大英语
932 日文
949 韩文
866 俄文
65001 unicode UFT-8
*/

测试

MySQL：

mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

mysql> drop table tmp1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tmp1 (name1 VARCHAR(9),name2 VARCHAR(9),name3 NVARCHAR(9),name4 NVARCHAR(9));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tmp1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tmp1  | CREATE TABLE `tmp1` (
  `name1` varchar(9) DEFAULT NULL,
  `name2` varchar(9) DEFAULT NULL,
  `name3` varchar(9) DEFAULT NULL,
  `name4` varchar(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tmp1 (NAME1, NAME2, NAME3, NAME4)
    -> VALUES ('123456789', '中国中中国中中国中', '123456789', '中国中中国中中国中');
Query OK, 1 row affected (0.00 sec)

mysql>  SELECT * FROM tmp1;
+-----------+-----------------------------+-----------+-----------------------------+
| name1     | name2                       | name3     | name4                       |
+-----------+-----------------------------+-----------+-----------------------------+
| 123456789 | 中国中中国中中国中          | 123456789 | 中国中中国中中国中          |
+-----------+-----------------------------+-----------+-----------------------------+
1 row in set (0.00 sec)

mysql>  SELECT CHAR_LENGTH(name1),CHAR_LENGTH(name2),CHAR_LENGTH(name3),CHAR_LENGTH(name4) FROM tmp1 t;
+--------------------+--------------------+--------------------+--------------------+
| CHAR_LENGTH(name1) | CHAR_LENGTH(name2) | CHAR_LENGTH(name3) | CHAR_LENGTH(name4) |
+--------------------+--------------------+--------------------+--------------------+
|                  9 |                  9 |                  9 |                  9 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql>  SELECT LENGTH(name1),LENGTH(name2),LENGTH(name3),LENGTH(name4) FROM tmp1 t;
+---------------+---------------+---------------+---------------+
| LENGTH(name1) | LENGTH(name2) | LENGTH(name3) | LENGTH(name4) |
+---------------+---------------+---------------+---------------+
|             9 |            27 |             9 |            27 |
+---------------+---------------+---------------+---------------+
1 row in set (0.01 sec)