>

use 数据库名,结尾的排序集是不区分大小写

- 编辑:澳门博发娱乐官网 -

use 数据库名,结尾的排序集是不区分大小写

一、基本操作

一. INT类型

mysql字符编码是本子4.1引进的,协理多国语言,而且有的特征已经超(英文名:jīng chāo)越了别的的数据库系统。

连接Mysql后,甘休一条sql语句要在背后加三个支行”;“。

1. INT类型的分类

  • TINYINT
    • 存款和储蓄空间 : 1 字节
    • 取值范围
      • 有符号(signed) : [-128, 127]
      • 无符号(unsigned) :[0, 255]
  • SMALLINT
    • 存款和储蓄空间 : 2 字节
    • 取值范围
      • 有符号(signed) : [-32768, 32767]
      • 无符号(unsigned) :[0, 65535]
  • MEDIUMINT
    • 仓库储存空间 : 3 字节
    • 取值范围
      • 有符号(signed) : [-8388608, 8388607]
      • 无符号(unsigned) :[0, 16777215]
  • INT
    • 储存空间 : 4 字节
    • 取值范围
      • 有符号(signed) : [-2147483648, 2147483647]
      • 无符号(unsigned) :[0, 4294967295]
  • BIGINT
    • 储存空间 : 8 字节
    • 取值范围
      • 有符号(signed) : [-9223372036854775808, 9223372036854775807]
      • 无符号(unsigned) :[0, 18446744073709551615]

能够在MySQL Command Line Client 下输入如下命令查看mysql的字符集

  • 成立数据库

    #在开创数量时,可以在数据库名前后加二个反引号(TAB键上的二个键),那样就能够用关键字成立一个数据库了,不然报错。 mysql> create database customer; Query OK, 1 row affected (0.00 sec)

  • 查看mysql版本

    mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.38 | +-----------+ 1 row in set (0.00 sec)

  • 查阅当前数据库

    mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec)

  • 翻开当前的记名顾客名

    mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)

  • 查阅当前的报到顾客名

    mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)

  • 翻开当前几天期时间

    mysql> select curtime(),curdate(),now(); +-----------+------------+---------------------+ | curtime() | curdate() | now() | +-----------+------------+---------------------+ | 15:43:50 | 2018-01-15 | 2018-01-15 15:43:50 | +-----------+------------+---------------------+ 1 row in set (0.00 sec)

  • 查阅有稍许数据库

    mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec)

  • 剔除一个点名的数据库

2. INT类型的使用

  • 自增长ID
    • 推荐使用BIGINT,而不是INT;
  • unsigned or signed

    • 依赖实际情状采纳,平日情状下推荐默认sigend
    • unsigned 的注意事项

    mysql> create database dbtype ; Query OK, 1 row affected (0.00 sec)

    mysql> use dbtype Database changed

    mysql> create table t_unsigned(a int unsigned,b int unsigned); Query OK, 0 rows affected (0.03 sec)

    mysql> insert into t_unsigned values(1,2); Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_unsigned; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+ 1 row in set (0.00 sec)

    mysql> select a - b from t_unsigned; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(dbtype.t_unsigned.a - dbtype.t_unsigned.b)' --表示高出数据范围

    mysql> set sql_mode = 'no_unsigned_subtraction'; --设置sql_mode模式 Query OK, 0 rows affected (0.00 sec)

    mysql> select a - b from t_unsigned; +-------+ | a - b | +-------+ | -1 | +-------+ 1 row in set (0.00 sec)

    mysql>

  • 相似情状下使用int时,推荐有暗号数(signed), 使用无符号数只是比原先多一倍的取值,数量级上尚无改换。

  • 如果须要取值当先12位以上,直接采纳用BIGINT类型

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.02 sec)

drop database 数据库名

3. INT(N)

mysql> show create table t_unsigned G;
*************************** 1. row ***************************
       Table: t_unsigned
Create Table: CREATE TABLE `t_unsigned` (
  `a` int(10) unsigned DEFAULT NULL,
  `b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
  • int(N) 和 zerofill

    • int(N)中的N是体现上涨的幅度,不表示仓库储存的数字的长度的上限。
    • zerofill意味着当存款和储蓄的数字长度 < N时,用数字0填充左侧,直至补满长度N
    • 当存款和储蓄数字的尺寸超过N时,按照实际存储的数显

    mysql> create table t_int_num(a int(3) zerofill); -- 显示上升的幅度N=3 Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_int_num values(10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_int_num values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_int_num;
+------+
| a    |
+------+
|  010 |     -- 不满 N=3时,左边用0填充
|  002 |
+------+
2 rows in set (0.00 sec)

mysql> insert into t_int_num values(444444);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_int_num;
+--------+
| a      |
+--------+
|    010 |
|    002 |
| 444444 |      -- 超过N=3的长度时,是什么数字,显示什么数字
+--------+
3 rows in set (0.00 sec)


mysql> select a, HEX(a) from t_int_numG
*************************** 1. row ***************************
     a: 010
HEX(a): A      -- 实际存储的还是十六进制A
*************************** 2. row ***************************
     a: 002
HEX(a): 2
*************************** 3. row ***************************
     a: 444444
HEX(a): 6C81C    -- 实际存储的还是十六进制444444
3 rows in set (0.00 sec)
  • int(N)中的Nzerofill匹配才有含义,且唯有是突显的时候才有意义,和骨子里存款和储蓄未有关联,不会去截取数字的长短。

MySQL 4.1的字符集协助(Character Set Support)有多少个方面:字符集(Character set)和排序格局(Collation)。对于字符集的支撑细化到四个档次: 服务器(server),数据库(database),数据表(table)和连续(connection)。
翻开系统的字符集和排序方式的设定能够因此下边包车型大巴两条命令:

  • 切换二个数据库为当前数据库

4. AUTO_INCREMENT

  • 自增
  • 每张表二个
  • 必需是索引的一有些

    mysql> create table t_auto_inc(a int auto_increment); EXC60RO奥迪Q5 1075 (4三千): Incorrect table definition; there can be only one auto column and it must be defined as a key --自增类必得为主键

    mysql> create table t_auto_inc(a int auto_increment primary key); Query OK, 0 rows affected (0.00 sec)

    mysql> insert into t_auto_inc values(null); --插入null值 Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc; +---+ | a | +---+ | 1 | -- 插入NULL值,便得以让其自增,且暗中同意从1早先+---+ 1 row in set (0.00 sec)

    mysql> insert into t_auto_inc values(1);
    E瑞虎RO兰德奔驰G级 1062 (2两千): Duplicate entry '1' for key 'P昂CoraIMA汉兰达Y' --自增配置主键必须为独一值

    mysql> insert into t_auto_inc values(2); Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc; +---+ | a | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(22); --插入数值,即使值越过原本自增值就能够用新值+1来代表 Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc; +----+ | a | +----+ | 1 | | 2 | | 22 | --形成自增值 +----+ 3 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(3); --插入值紧跟于自增值22所以还是没变 Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc; +----+ | a | +----+ | 1 | | 2 | | 3 |
    | 22 | --自增值不变+----+ 4 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(null); --当插如null值 Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_auto_inc values(null); --再一次插入null值 Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc;
    +----+ | a | +----+ | 1 | | 2 | | 3 | | 22 | | 23 | --自增值+1 | 24 | --自增值再+1 +----+ 6 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(20); --插入20值小于自增值24 Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc; +----+ | a | +----+ | 1 | | 2 | | 3 | | 20 | --插入到自增值后面| 22 | | 23 | | 24 | +----+ 7 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(-1); --插入-1 Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc; +----+ | a | +----+ | -1 | | 1 | | 2 | | 3 | | 20 | | 22 | | 23 | | 24 | +----+ 8 rows in set (0.00 sec)

mysql> insert into t_auto_inc values('0');
Query OK, 1 row affected (0.00 sec)

mysql> update t_auto_inc set a =0 where a =-1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
| 20 |
| 22 |
| 23 |
| 24 |
+----+
8 rows in set (0.00 sec)

mysql> insert into t_auto_inc values(0);  --数字 0 这个值比较特殊, 插入0和插入NULL的效果是一样的,都是代表自增
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
| 20 |
| 22 |
| 23 |
| 24 |
| 26 |
+----+
9 rows in set (0.00 sec)
  • AUTO_INCREMENT是实例运营时,取当前表的最大值,然后 +1 即为下一次自增的值。(MAX + 1)

  • 注意: insert into tablename select NULL; 等价与 insert into tablename values (NULL);


mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:MySQLMySQL Server 5.0sharecharsets |
+--------------------------+-------------------------------------------+
8 rows in set (0.06 sec)

use 数据库名

二. 数字类型

mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.02 sec)

mysql> use test
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

1. 数字类型的分类

  • 单精度类型:FLOAT
    • 积攒空间:4 字节
    • 精确性:低
  • 双精度类型:DOUBLE
    • 占用空间:8 字节
    • 精确性:低,比FLOAT高
  • 高精度类型:DESagitarL
    • 攻下空间:变长
    • 精确性:非常高

介怀:财务系统必须使用DECIMAL


地点列出的值就是系统的暗许值。latin1默许查对法规是latin1_swedish_ci,私下认可是latin1的波兰语排序方式. 为何呢暗许会是latin1_swedish_ci呢,追溯一下mysql历史很轻便开掘.

  • 查阅字符集

    mysql> show variables like 'char%'; +--------------------------+--------------------------+ | Variable_name | Value | +--------------------------+--------------------------+ | character_set_client | gbk | | character_set_connection | gbk | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | gbk | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | c:mysqlsharecharsets | +--------------------------+--------------------------+ 8 rows in set (0.00 sec)

三. 字符串类型

壹玖柒陆 年,一家瑞典王国公司Tcx欲开拓八个快捷的二十三十二线程、多客户数据库系统。Tcx 公司初叶想利用mSQL和她俩自身的火速低端例程 (Indexed Sequential Access Method,ISAM)去老是数据库表,可是,在局地测量检验之后得出结论:mSQL对其须求来讲缺乏急速和灵活。那就产生了多少个连接器数据库的新SQL接 口,它利用大约和mSQL同样的API接口。那么些API被设计成能够使那个由mSQL而写的第三方代码更易于地移植到MySQL。

  • 设置连接服务器及当前客商端的字符集

    mysql> set names utf8; Query OK, 0 rows affected (0.00 sec)

  • 查阅当前劳动支撑的字符集

    mysql> show charset; +----------+-----------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+-----------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +----------+-----------------------------+---------------------+--------+ 40 rows in set (0.02 sec)

  • 翻开数据库引擎

    mysql> show engines; +--------------------+---------+------------------------------------------------ ----------------+--------------+------+------------+ | Engine | Support | Comment

                | Transactions | XA   | Savepoints |
    

    +--------------------+---------+------------------------------------------------ ----------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine

                | NULL         | NULL | NULL       |
    

    | MRG_MYISAM | YES | Collection of identical MyISAM tables

                | NO           | NO   | NO         |
    

    | MyISAM | YES | MyISAM storage engine

                | NO           | NO   | NO         |
    

    | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine

                | NO           | NO   | NO         |
    

    | MEMORY | YES | Hash based, stored in memory, useful for tempor ary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine

                | NO           | NO   | NO         |
    

    | InnoDB | DEFAULT | Supports transactions, row-level locking, and f oreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema

                | NO           | NO   | NO         |
    

    +--------------------+---------+------------------------------------------------ ----------------+--------------+------+------------+ 9 rows in set (0.01 sec)

  • 修改数据库的字符集

    mysql> alter database db character set utf8; Query OK, 1 row affected (0.00 sec)

1. 字符串类型介绍

类型 说明 N的含义 是否有字符集 最大长度
CHAR(N) 定长字符 字符 255
VARCHAR(N) 变长字符 字符 16384
BINARY(N) 定长二进制字节 字节 255
VARBINARY(N) 变长二进制字节 字节 16384
TINYBLOB(N) 二进制大对象 字节 256
BLOB(N) 二进制大对象 字节 16K
MEDIUMBLOB(N) 二进制大对象 字节 16M
LONGBLOB(N) 二进制大对象 字节 4G
TINYTEXT(N) 大对象 字节 256
TEXT(N) 大对象 字节 16K
MEDIUMTEXT(N) 大对象 字节 16M
LONGTEXT(N) 大对象 字节 4G

当然也能够供给修改mysql的暗许字符集
在mysql配置文书档案my.ini,找到如下两句:

   

2. N和字符集

  • char(N)

    • 万一当前table的字符集的最大长度W, 则char(N)的最大存款和储蓄空间为 (N X W)Byte;纵然使用UTF-8,则char(10)可以非常的小存款和储蓄12个字节的字符,最大存款和储蓄贰21个字节的字符,其实是另一种意义上的varchar
    • 当存款和储蓄的字符数小于N时,尾部使用空格填充,何况填充最小字节的空格

    mysql> create table t_char(a char(10)); Query OK, 0 rows affected (0.01 sec)

    mysql> show create table t_char G; *** 1. row ***

       Table: t_char
    

    Create Table: CREATE TABLE t_char ( a char(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

    mysql> insert into t_char values('abc'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values('你好哦'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values('你好abc'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values('abc你好'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values('很不好abc'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values('很不abc好'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values('很倒霉abc'); Query OK, 1 row affected (0.00 sec)

    mysql> select a, length(a) from test_char; ERROR 1146 (42S02): Table 'dbtype.test_char' doesn't exist mysql> select a, length(a) from t_char; +-----------------+-----------+ | a | length(a) | +-----------------+-----------+ | abc | 3 | | 你好啊 | 9 | | 你好abc | 9 | | abc你好 | 9 | | 很倒霉abc | 12 | | 特别不abc好 | 12 | | 非常不好abc | 15 | +-----------------+-----------+ 7 rows in set (0.00 sec)

    mysql> select a,hex(a) from t_char; +-----------------+--------------------------------+ | a | hex(a) | +-----------------+--------------------------------+ | abc | 616263 | -- 注意这里,以及下边包车型地铁16进制值,一会得以对照 | 你好哦 | E4BDA0E5A5BDE593A6 | | 你好abc | E4BDA0E5A5BD616263 | | abc你好 | 616263E4BDA0E5A5BD | | 很倒霉abc | E5BE88E4B88DE5A5BD616263 | | 非常不abc好 | E5BE88E4B88D616263E5A5BD | | 很倒霉abc | E99D9EE5B8B8E4B88DE5A5BD616263 | +-----------------+--------------------------------+ 7 rows in set (0.00 sec)

    mysql> select hex(' '); +----------+ | hex(' ') | +----------+ | 20 | -- 注意空格,空格对应的16进制数字是 20 +----------+ 1 row in set (0.00 sec)

t_char表实际二进制存款和储蓄文件

-- shell> hexdump -C t_char.idb

0000c070  73 75 70 72 65 6d 75 6d  0a 00 00 00 10 00 24 00  |supremum......$.|
0000c080  00 00 00 12 04 00 00 40  18 07 2c b8 00 01 df 9c  |.......@..,.....|
0000c090  01 10 61 62 63 20 20 20  20 20 20 20 0a 00 00 00  |..abc       ....| --abc为61 62 63 空格为20 后面补了7个空格也就是 '61 62 63 20 20 20  20 20 20 20'
0000c0a0  18 00 24 00 00 00 00 12  05 00 00 40 18 07 2d b9  |..$........@..-.|
0000c0b0  00 01 df 9d 01 10 e4 bd  a0 e5 a5 bd e5 93 a6 20  |............... | --你好哦表示  e4 bd a0 e5 a5 bd e5 93 a6 20 后面补了空格20
0000c0c0  0a 00 00 00 20 00 24 00  00 00 00 12 06 00 00 40  |.... .$........@|
0000c0d0  18 07 32 bc 00 01 df b0  01 10 e4 bd a0 e5 a5 bd  |..2.............| --你好abc表示 e4 bd a0 e5 a5 bd 61 62 63 20 后面补了空格20
0000c0e0  61 62 63 20 0a 00 00 00  28 00 24 00 00 00 00 12  |abc ....(.$.....|
0000c0f0  07 00 00 40 18 07 33 bd  00 01 df a1 01 10 61 62  |...@..3.......ab| --abc你好表示 61 62 63 e4 bd a0 e5 a5 bd 20 后面补了空格20
0000c100  63 e4 bd a0 e5 a5 bd 20  0c 00 00 00 30 00 26 00  |c...... ....0.&.|
0000c110  00 00 00 12 08 00 00 40  18 07 3f a7 00 01 df 97  |.......@..?.....| 
0000c120  01 10 e5 be 88 e4 b8 8d  e5 a5 bd 61 62 63 0c 00  |...........abc..| --很不好abc表示 e5 be 88 e4 b8 8d e5 a5 bd 61 62 63 没有空格补
0000c130  00 00 38 00 26 00 00 00  00 12 09 00 00 40 18 07  |..8.&........@..|
0000c140  40 a8 00 01 df 8d 01 10  e5 be 88 e4 b8 8d 61 62  |@.............ab| --很不abc好表示 e5 be 88 e4 b8 8d 61 62 63 e5 a5 bd 没有空格补
0000c150  63 e5 a5 bd 0f 00 00 00  40 ff 15 00 00 00 00 12  |c.......@.......|
0000c160  0a 00 00 40 18 07 4c b2  00 01 df 81 01 10 e9 9d  |...@..L.........|
0000c170  9e e5 b8 b8 e4 b8 8d e5  a5 bd 61 62 63 00 00 00  |..........abc...|
0000c180  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
  • varchar(N)

    mysql> create table tt_varchar(a varchar(10)); Query OK, 0 rows affected (0.12 sec)

    mysql> show create table t_varchar G; *** 1. row ***

       Table: t_varchar
    

    Create Table: CREATE TABLE t_varchar ( a varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

    mysql> insert into t_varchar values('abc'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_varchar values('abc你好'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_varchar values('很不好abc'); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_varchar values('很不好abc'); Query OK, 1 row affected (0.00 sec)

    mysql> select a, length(a) from t_varchar; +-----------------+-----------+ | a | length(a) | +-----------------+-----------+ | abc | 3 | | abc你好 | 9 | | 特别不佳abc | 12 | | 非常糟糕abc | 15 | +-----------------+-----------+ 4 rows in set (0.00 sec)

    mysql> select a,hex(a) from t_varchar; +-----------------+--------------------------------+ | a | hex(a) | +-----------------+--------------------------------+ | abc | 616263 | | abc你好 | 616263E4BDA0E5A5BD | | 很倒霉abc | E5BE88E4B88DE5A5BD616263 | | 非常倒霉abc | E99D9EE5B8B8E4B88DE5A5BD616263 | +-----------------+--------------------------------+ 4 rows in set (0.00 sec)

t_varchar表实际二进制存款和储蓄文件

-- shell> hexdump -C t_char.idb
-- 和char一样观察,都没有进行空格的填充

0000c070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1d 00  |supremum........|
0000c080  00 00 00 12 0f 00 00 40  18 08 17 fb 00 01 df 8a  |.......@........|
0000c090  01 10 61 62 63 09 00 00  00 18 00 23 00 00 00 00  |..abc......#....|
0000c0a0  12 10 00 00 40 18 08 18  fc 00 01 df b5 01 10 61  |....@..........a|
0000c0b0  62 63 e4 bd a0 e5 a5 bd  0c 00 00 00 20 00 26 00  |bc.......... .&.|
0000c0c0  00 00 00 12 11 00 00 40  18 08 1d ff 00 01 df 8b  |.......@........|
0000c0d0  01 10 e5 be 88 e4 b8 8d  e5 a5 bd 61 62 63 0f 00  |...........abc..|
0000c0e0  00 00 28 ff 8b 00 00 00  00 12 12 00 00 40 18 08  |..(..........@..|
0000c0f0  1e 80 00 01 df b6 01 10  e9 9d 9e e5 b8 b8 e4 b8  |................|
0000c100  8d e5 a5 bd 61 62 63 00  00 00 00 00 00 00 00 00  |....abc.........|
0000c110  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
  • 安插数据尾部带空格

    mysql> insert into test_char values('好好好 '); -- 后面有3个空格 Query OK, 1 row affected (0.03 sec)

    mysql> insert into test_varchar values('好好好 '); -- 后面有3个空格 Query OK, 1 row affected (0.02 sec)

    --

    -- t_char 表

    mysql> select a, length(a) from t_char; +-----------------+-----------+ | a | length(a) | +-----------------+-----------+ | abc | 3 | | 你好哦 | 9 | | 你好abc | 9 | | abc你好 | 9 | | 很倒霉abc | 12 | | 特别不abc好 | 12 | | 特不佳abc | 15 | | 开黑中 | 9 | --9个字节 | 你好啊 | 9 | +-----------------+-----------+ 9 rows in set (0.00 sec)

    mysql> select a,hex(a) from t_char; +-----------------+--------------------------------+ | a | hex(a) | +-----------------+--------------------------------+ | abc | 616263 | | 你好啊 | E4BDA0E5A5BDE593A6 | | 你好abc | E4BDA0E5A5BD616263 | | abc你好 | 616263E4BDA0E5A5BD | | 很倒霉abc | E5BE88E4B88DE5A5BD616263 | | 特不abc好 | E5BE88E4B88D616263E5A5BD | | 特不佳abc | E99D9EE5B8B8E4B88DE5A5BD616263 | | 开黑中 | E5BC80E9BB91E4B8AD | | 你好啊 | E4BDA0E5A5BDE5958A | --无填充 +-----------------+--------------------------------+ 9 rows in set (0.00 sec)

    --

    -- t_varchar表

    mysql> insert into t_varchar values('你好啊 '); Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_varchar values('开黑中 '); Query OK, 1 row affected (0.00 sec)

    mysql> select a, length(a) from t_varchar; +-----------------+-----------+ | a | length(a) | +-----------------+-----------+ | abc | 3 | | abc你好 | 9 | | 特不佳abc | 12 | | 比比较差abc | 15 | | 你好啊 | 12 | -- (你好啊)9个字节 + 3个字节的空格 | 开黑中 | 12 | +-----------------+-----------+ 6 rows in set (0.00 sec)

    mysql> select a,hex(a) from t_varchar; +-----------------+--------------------------------+ | a | hex(a) | +-----------------+--------------------------------+ | abc | 616263 | | abc你好 | 616263E4BDA0E5A5BD | | 相当差abc | E5BE88E4B88DE5A5BD616263 | | 特别不好abc | E99D9EE5B8B8E4B88DE5A5BD616263 | | 你好啊 | E4BDA0E5A5BDE5958A202020 | -- 前面有20 20 20 ,表示3个和煦的空格 | 开黑中 | E5BC80E9BB91E4B8AD202020 |
    +-----------------+--------------------------------+ 6 rows in set (0.00 sec)

地方的气象不也许用统一的准则进行表述,可是合葡萄牙语档付给的分解是,那样的安顿是为着制止索引页的零碎

[mysql]

3.BLOB和TEXT

  • 在BLOB和TEXT上创办索引时,必需钦定索引前缀的长度

    mysql> create table t_text(a int primary key, b text, key(b)); ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key length

    mysql> create table t_text(a int primary key, b text, key(b(64))); Query OK, 0 rows affected (0.00 sec)

  • BLOB和TEXT列无法有暗中同意值

  • BLOB和TEXT列排序时只行使该列的前max_sort_length个字节

    mysql> select @@max_sort_length; +-------------------+ | @@max_sort_length | +-------------------+ | 1024 | +-------------------+ 1 row in set (0.00 sec)

不建议在MySQL中蕴藏大型的二进制数据,比如歌曲,录制


default-character-set=latin1

四. 字符集

1. 广阔的字符集

  • utf8
  • utf8mb4
  • gbk
  • gb18030

    mysql> show character set; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | -- gbk,表示的字符有限 | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | -- utf8,最长3字节 | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | -- utf8 + mobile端字符 | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | -- gb18030,最长4个字节 +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec)

# created and no character set is defined
default-character-set=latin1

本文由胜博发-操作发布,转载请注明来源:use 数据库名,结尾的排序集是不区分大小写