库表 - IPS99技术分享

标签：ci utf8mb4 SPACE NO 库表 PAD Yes

规约

建立表的时候应该指定表空间和表索引，不然一般建立的表就是default，直接在默认的空间之下。
boolen类型的column应该遵循1表示是，0表示否。
精度丢失的问题，小数比较和运算的时候应该以Decimal进行。

字符串

charh和varchar表示定长和变长字符串。
charh和varchar的长度限制：254byte和2^14大小。
varchar能够在家在的时候将超长的部分切断，不会进行报错，char就会出错。

索引问题

索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。简单说来，索引就是指向表中数据的指针。数据库的索引同书籍后面的索引非常相像。

索引用于加快查询数据的速度，一般来说会加快select和where子句的查询速度，而会减慢update和insert子句的操作速度。
小的数据表不应当使用索引。
需要频繁进行大批量的更新或者插入操作的表。
如果列中包含大数或者 NULL 值，不宜创建索引。
频繁操作的列不宜创建索引。

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
`TINYINT`	1 Bytes	(-128，127)	(0，255)	小整数值
`SMALLINT`	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
`MEDIUMINT`	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
`INT`或`INTEGER`	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
`BIGINT`	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
`FLOAT`	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
`DOUBLE`	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
`DECIMAL`	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

字符串类型

类型	大小	用途
`CHAR`	0-255 bytes	定长字符串
`VARCHAR`	0-65535 bytes	变长字符串
`TINYBLOB`	0-255 bytes	不超过 255 个字符的二进制字符串
`TINYTEXT`	0-255 bytes	短文本字符串
`BLOB`	0-65 535 bytes	二进制形式的长文本数据
`TEXT`	0-65 535 bytes	长文本数据
`MEDIUMBLOB`	0-16 777 215 bytes	二进制形式的中等长度文本数据
`MEDIUMTEXT`	0-16 777 215 bytes	中等长度文本数据
`LONGBLOB`	0-4 294 967 295 bytes	二进制形式的极大文本数据
`LONGTEXT`	0-4 294 967 295 bytes	极大文本数据

MYSQL `LIKE`

SQL的该子句的语法指的是模糊查询，相比于=查询的结果是精确的查询，like的工作是根据提供的条件进行模糊查询工作，字符串匹配的原则如下：

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

`GROUP BY` 与 `WITH ROLLUP`

group by语句将表中的数据根据当前的指定的列进行分组，并结合count()|sun()|avg()函数进行统计，展示当前的表条信息；而with rollup则可以根据当前分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…），展示到表信息当中。

mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | signin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

mysql> SELECT name, SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | signin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

可以使用函数coalesec()函数进行取代null的名称，其函数的语法是：

Coalesce(item_A,item_B,item_C)；

如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | signin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)

排序 `ORDER BY`

order by 的排序根据指定的升序和降序规则将检索的信息进行排序并展出。asc表示升序，也是默认default方式，而desc则是进行降序的排序，这个需要特别指定。

连接语句

inner join | left join | right join是sql的连接语句，他们将两张表的相关条目进行连接，连接起来的条目展示在当前的展示信息当中。
其中left join表示左连接，不管右表有无相关的记录，将获取左表的相关所有记录，right join则是不管左表有没有相关的记录，都将获得右表的所有信息，inner join则是进行内部链接，获取两张表当中字段匹配关系的相关记录，这个操作一般用来进行信息的匹配，可以根据两张表中的关联信息，将用户或者其他任何机构的相关联信息同时展示在同一张表格当中，用于相关信息的提取。
inner join的实际作用跟join是一样的，不过inner join实际上与where子句所产生的效果在一般情况之下没什么分别，所以一般情况之下使用where子句进行操作。

-- ------------------------------
-- inner join:
mysql> select * from company_staff;
+----+-----------+-------+
| id | name      | grade |
+----+-----------+-------+
|  1 | 小明      | A     |
|  2 | 灰太狼    | B     |
|  3 | 红太狼    | A     |
|  4 | 小红      | B     |
|  5 | 小明      | A     |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | signin |
+----+--------+---------------------+--------+
|  1 | 小明   | 2016-04-22 15:25:33 |      1 |
|  2 | 小王   | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽   | 2016-04-19 15:26:02 |      2 |
|  4 | 小王   | 2016-04-07 15:26:14 |      4 |
|  5 | 小明   | 2016-04-11 15:26:40 |      4 |
|  6 | 小明   | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

mysql> select a.id,a.name,a.grade from company_staff a join employee_tbl b on  a.id=b.id;
+----+-----------+-------+
| id | name      | grade |
+----+-----------+-------+
|  1 | 小明      | A     |
|  2 | 灰太狼    | B     |
|  3 | 红太狼    | A     |
|  4 | 小红      | B     |
|  5 | 小明      | A     |
+----+-----------+-------+
5 rows in set (0.01 sec)
-- left join:
left join 子句相当于从A和B的全集中取得左边的集合A的全部内容（即使筛选条件当中相交的部分不存在或者缺少部分内容）。
right join 则是和left join 相反，取右边的集合的全部。

正则表达式

模式	描述
`^`	匹配输入字符串的开始位置。如果设置了 `RegExp` 对象的 `Multiline` 属性，`^` 也匹配 `'\n'` 或`'\r'`之后的位置。
`$`	匹配输入字符串的结束位置。如果设置了`RegExp` 对象的 `Multiline` 属性，`$` 也匹配 `'\n'` 或 `'\r'` 之前的位置。
`.`	匹配除 `"\n"` 之外的任何单个字符。要匹配包括 `'\n'` 在内的任何字符，请使用像 `'[.\n]'` 的模式。
`[...]`	字符集合。匹配所包含的任意一个字符。例如， `'[abc]'` 可以匹配 `"plain"`中的 `'a'`。
`[^...]`	负值字符集合。匹配未包含的任意字符。例如， `'[^abc]'` 可以匹配 `"plain"` 中的`'p'`。
`p1	p2
`*`	匹配前面的子表达式零次或多次。例如，`zo` 能匹配 `"z"` 以及 `"zoo"`。`` 等价于`{0,}`。
`+`	匹配前面的子表达式一次或多次。例如，`'zo+'` 能匹配 `"zo"` 以及 `"zoo"`，但不能匹配 `"z"`。`+` 等价于 `{1,}`。
`{n}`	`n` 是一个非负整数。匹配确定的 `n` 次。例如，`'o{2}'` 不能匹配 `"Bob"` 中的 `'o'`，但是能匹配 `"food"` 中的两个 `o`。
`{n,m}`	`m` 和`n`均为非负整数，其中`n <= m`。最少匹配 `n` 次且最多匹配 `m` 次。

`ALTER`关键字

需要修改表的字段名称、增加或者删除表的字段、修改表的名称、以及修改相应字段的信息（包含数据类型、默认值等等）都需要用到alter关键字。

-- ------------------------------
-- 首先查看表的结构column:
mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int     | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
-- 表的字段的名称和数据类型如上所示。

-- ------------------------------
-- 增加表的字段名称：
mysql> alter table testalter_tbl add a int first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int     | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
| i     | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-- 上面的语句当中我们增加了一个名叫a的字段名字在表头，注意：一般不指定字段的其他信息的话，default的值一般就是null，我们可以通过下面的语句来指定当前的表字段要加在表的哪个部位：
first:增加在表头
after:增加在指定的表字段的后面
mysql> alter table testalter_tbl add u int after c;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int     | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
| u     | int     | YES  |     | NULL    |       |
| i     | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- ------------------------------
-- 删除表的字段：
mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int     | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table testalter_tbl drop i;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
-- 通过drop关键字来删除表中的响应字段信息。

-- ------------------------------
-- 修改表中的字段的信息
mysql> alter table testalter_tbl modify a bigint not null default 100;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | bigint  | NO   |     | 100     |       |
| c     | char(1) | YES  |     | NULL    |       |
| u     | int     | YES  |     | NULL    |       |
| i     | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table testalter_tbl change c t bigint not null default 100;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| t     | bigint | NO   |     | 100     |       |
| u     | int    | YES  |     | NULL    |       |
| i     | int    | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.00 sec)
-- 修改表的字段信息可以用到的关键字是：
modify:修改指定的表字段的数据类型和默认值
change:该关键字能够修改表字段的名称、数据类型和默认值

-- ------------------------------
-- 修改默认值
mysql> alter table testalter_tbl alter u set default 100;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| t     | bigint | NO   |     | 100     |       |
| u     | int    | YES  |     | 100     |       |
| i     | int    | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.01 sec)
-- 修改相应字段的默认值不需要冲洗指定字段的数据类型，通过alter关键字就可以修改相应的默认值。

-- ------------------------------
-- 修改table name
mysql> alter table testalter_tbl rename to aaa;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_runoob |
+------------------+
| aaa              |
| apps             |
| company_staff    |
| employee_tbl     |
| websites         |
+------------------+
5 rows in set (0.01 sec)
-- 通过rename to 关键字我们可以修改表的名字。

-- ------------------------------
-- 修改表的字段相对位置
mysql> show columns from testalter_tbl;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| t     | bigint | NO   |     | 100     |       |
| u     | int    | YES  |     | 100     |       |
| i     | int    | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table testalter_tbl modify i int after a;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| i     | int    | YES  |     | NULL    |       |
| t     | bigint | NO   |     | 100     |       |
| u     | int    | YES  |     | 100     |       |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.00 sec)
-- 只要是表中存在的字段，modify关键字通过修改可以改变相对位置。

-- ------------------------------
-- 修改存储引擎：修改为myisam
alter table tableName engine=myisam;

-- ------------------------------
-- 删除外键约束：keyName是外键别名
alter table tableName drop foreign key keyName;

`SHOW STATUS`

-- ------------------------------
mysql> show table status like 'testalter_tbl'\G;
*************************** 1. row ***************************
           Name: testalter_tbl
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-12-28 17:31:25
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
-- 该关键字查看当前表的各种信息。

`INDEX索引`

索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引，即一个索引包含多个列。

对于索引而言，一般情况之下它会使得查询加速，但是大量使用索引不适用于被频繁使用的列，这样会增加负载，在需要被频发更新或者删除的列上面也不宜使用索引，原因显而易见。

-- ------------------------------
-- 创建索引
mysql> create index refer on testalter_tbl(a);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   | MUL | 100     |       |
| i     | int    | YES  |     | NULL    |       |
| t     | bigint | NO   |     | 100     |       |
| u     | int    | YES  |     | 100     |       |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.01 sec)

-- ------------------------------
-- 修改表的结构
ALTER table tableName ADD INDEX indexName(columnName)

-- ------------------------------
-- 删除索引
mysql> drop index refer on testalter_tbl;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from testalter_tbl;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| i     | int    | YES  |     | NULL    |       |
| t     | bigint | NO   |     | 100     |       |
| u     | int    | YES  |     | 100     |       |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- ------------------------------
-- alter修改索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

临时表`TEMPORARY TABLE`

-- ------------------------------
-- 创建临时表，该表在断开会话框的时候会消失，数据不会保存。
CREATE TEMPORARY TABLE SalesSummary;

`TABLE CLONE`

-- ------------------------------
-- 复制一个表的信息到一个新的表
mysql> show columns from clone_tbl;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| i     | int    | YES  |     | NULL    |       |
| t     | bigint | NO   |     | 100     |       |
| u     | int    | YES  |     | 100     |       |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> create table clonePart like clone_tbl;
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from clonePart;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| i     | int    | YES  |     | NULL    |       |
| t     | bigint | NO   |     | 100     |       |
| u     | int    | YES  |     | 100     |       |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into clonePart select * from clone_tbl;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
-- 使用like关键字创建一个一样的表，并将之前的表的信息select出来插入到新的表当中。

-- ------------------------------
-- 复制一部分数据到新的表当中
mysql> create table clone1 as (select a,i from clone_tbl);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show columns from clone1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| i     | int    | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.01 sec)

-- ------------------------------
-- 在新建的表当中指定信息

mysql> create table new_clone (a int(10) not null auto_increment primary key) as (select * from clone1);
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show columns from clone1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| a     | bigint | NO   |     | 100     |       |
| i     | int    | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show columns from new_clone;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| a     | int  | NO   | PRI | NULL    | auto_increment |
| i     | int  | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

SQL元数据

select version(); -- 查看服务器的版本
select user(); -- 查看当前的操作用户
select database(); -- 查看数据库
select status(); --查看服务器的状态
select variables(); -- 查看服务器配置变量

`AUTO_INCREMENT`序列

-- -----------------------------------
-- 使用序列
mysql> alter table new_clone add id int(100) not null primary key auto_increment first;
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show columns from new_clone;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| a     | int  | NO   |     | NULL    |                |
| i     | int  | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

-- -----------------------------------
-- 删除自增的主键和列
mysql> alter table new_clone drop id;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show columns from new_clone;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     | int  | NO   |     | NULL    |       |
| i     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- -----------------------------------
-- 重置序列：首先删除主键和列然后重新指定就可以
mysql> alter table new_clone add id int(100) not null primary key auto_increment first;
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show columns from new_clone;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| a     | int  | NO   |     | NULL    |                |
| i     | int  | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

-- -----------------------------------
-- 获得最后一次insert的具有自增序列的id值：
`last_insert_id()`函数
mysql> select * from new_clone;
+----+---+------+
| id | a | i    |
+----+---+------+
|  1 | 1 |    1 |
|  2 | 2 |    3 |
+----+---+------+
2 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

删除`PRIMARY KEY`

删除primary key存在两种情况：

不存在任何约束

alter table new_clone drop primary key;

存在auto_increment约束

这种情况之下，上面的语法无法直接删除主键，需要将auto_increment约束解除，再删除主键。

mysql> show columns from new_clone;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| a     | int  | NO   | PRI | NULL    | auto_increment |
| i     | int  | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

-- 去除约束
mysql> alter table new_clone modify a int;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show columns from new_clone;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     | int  | NO   | PRI | NULL    |       |
| i     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 删除主键
mysql> alter table new_clone drop primary key;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show columns from new_clone;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     | int  | NO   |     | NULL    |       |
| i     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

数据重复处理

采用`primary key、unique`

防止相应的列出现重复的数据，这种情况之下，如果在insert into的时候存在相同的列数据，就会报错，那么解决办法是：

1. insert ignore into table new_clone values(...);
2. insert replace into table new_clone values(...);

上面的两种方法一种是如果重复，直接忽略该条数据的插入，第二种如果重复，先删除之前的数据然后再插入新的数据，显然不建议这样做，增加了insert｜delete成本。

统计重复的数据

mysql> select * from new_clone;
+----+---+------+
| id | a | i    |
+----+---+------+
|  1 | 1 |    1 |
|  2 | 2 |    3 |
|  3 | 2 |    3 |
+----+---+------+
3 rows in set (0.00 sec)

-- 筛选可能重复的数据统计结果展示在epetitions列：
mysql> select count(*) as epetitions,a,i from new_clone group by a,i having epetitions>1;
+------------+---+------+
| epetitions | a | i    |
+------------+---+------+
|          2 | 2 |    3 |
+------------+---+------+
1 row in set (0.00 sec)

mysql> select * from new_clone;
+----+---+------+
| id | a | i    |
+----+---+------+
|  1 | 1 |    1 |
|  2 | 2 |    3 |
|  3 | 2 |    3 |
|  4 | 2 |    4 |
+----+---+------+
4 rows in set (0.01 sec)

-- 一旦选择
mysql> select count(*) as epetitions,a,i from new_clone group by a,i having epetitions>1;
+------------+---+------+
| epetitions | a | i    |
+------------+---+------+
|          2 | 2 |    3 |
+------------+---+------+
1 row in set (0.00 sec)

上述统计的步骤一般应该包含：

- 确定哪一列包含的值可能会重复。
- 在列选择列表使用COUNT(*)列出的那些列。
- 在GROUP BY子句中列出的列。
- HAVING子句设置重复数大于1。

过滤重复数据`DISTINCT`

mysql> select * from new_clone;
+----+---+------+
| id | a | i    |
+----+---+------+
|  1 | 1 |    1 |
|  2 | 2 |    3 |
|  3 | 2 |    3 |
|  4 | 2 |    4 |
+----+---+------+
4 rows in set (0.01 sec)
mysql> select distinct a,i from new_clone;
+---+------+
| a | i    |
+---+------+
| 1 |    1 |
| 2 |    3 |
| 2 |    4 |
+---+------+
3 rows in set (0.00 sec)
-- 上述结果表示：distinct挑选的结果将以最多的符合列展出，确保不会因此丢掉任何信息。

删除重复数据

删除数据最简单的办法就是：通过distinct子句筛选出原来表当中的数据（未曾重复）重新创建一张新的表，然后删除原来的表，再将新表更名。但是对于数据量很大，重复不多的table来说，这种操作无疑是对于IO资源的毁灭性打击。不可取，小数据table可行。

mysql> create table tmp select distinct * from new_clone;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> drop table new_clone;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table tmp rename to new_clone;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from new_clone;
+---+------+
| a | i    |
+---+------+
| 1 |    1 |
| 2 |    3 |
| 2 |    4 |
+---+------+
3 rows in set (0.00 sec)

上述结果对于一般的tiny table或者数据量重复很多的稍大的表能够使用，但是一旦涉及到数据量比较大的table，尤其是其重复数据量处于一个delete无法很快处理的境地，就需要更加有效的方法：

查询重复的数据：

mysql> select 
    -> name, sex, age, count(*) as count
    -> from user_info 
    -> group by name, sex, age 
    -> having count(*) > 1;
+------+------+------+-------+
| name | sex  | age  | count |
+------+------+------+-------+
| A    | 男   |   22 |     3 |
| B    | 女   |   33 |     3 |
| C    | 男   |   44 |     2 |
| D    | 女   |   55 |     2 |
+------+------+------+-------+
4 rows in set (0.00 sec)

查找需要保留的数据（以上默认id最小的值）：

select * from user_info 
where id in (
	select 
		min(id) 
	from user_info 
	group by name, sex, age
);
+----+------+------+------+------------+
| id | name | sex  | age  | remark     |
+----+------+------+------+------------+
|  1 | A    | 男   |   22 | 第一个A    |
|  2 | B    | 女   |   33 | 第一个B    |
|  3 | C    | 男   |   44 | 第一个C    |
|  4 | D    | 女   |   55 | 第一个D    |
|  9 | E    | 男   |   18 | 第一个E    |
| 12 | F    | 男   |   15 | 第一个F    |
+----+------+------+------+------------+
6 rows in set (0.00 sec)

最终确保要删除的数据是：in重复数据当中且not in min_id当中。

第二种方法是：找到那些最小的id的值，其余的全部删除，这样来说的话对于查询工作节省了很所时间：

delete from user_info 
where id not in(
	select min_id from (
		select 
			min(id) as min_id
		from user_info 
		group by name, sex, age
	) as x
);
+----+------+------+------+------------+
| id | name | sex  | age  | remark     |
+----+------+------+------+------------+
|  1 | A    | 男   |   22 | 第一个A    |
|  2 | B    | 女   |   33 | 第一个B    |
|  3 | C    | 男   |   44 | 第一个C    |
|  4 | D    | 女   |   55 | 第一个D    |
|  9 | E    | 男   |   18 | 第一个E    |
| 12 | F    | 男   |   15 | 第一个F    |
+----+------+------+------+------------+
6 rows in set (0.00 sec)

Query OK, 6 rows affected (0.01 sec)

最后一种：inner join方式：自己和自己关联进行删除：

delete a.*
from user_info as a 
inner join user_info as b 
on a.name = b.name 
and a.sex = b.sex 
and a.age = b.age
and  a.id > b.id;

mysql> select * from user_info; 
+----+------+------+------+------------+
| id | name | sex  | age  | remark     |
+----+------+------+------+------------+
|  1 | A    | 男   |   22 | 第一个A    |
|  2 | B    | 女   |   33 | 第一个B    |
|  3 | C    | 男   |   44 | 第一个C    |
|  4 | D    | 女   |   55 | 第一个D    |
|  9 | E    | 男   |   18 | 第一个E    |
| 12 | F    | 男   |   15 | 第一个F    |
+----+------+------+------+------------+
6 rows in set (0.00 sec)

`SQL`注入

SQL注入方式

如果您通过网页获取用户输入的数据并将其插入一个MySQL数据库，那么就有可能发生SQL注入安全的问题。

本章节将为大家介绍如何防止SQL注入，并通过脚本来过滤SQL中注入的字符。

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

以下实例中，输入的用户名必须为字母、数字及下划线的组合，且用户名长度为 8 到 20 个字符之间

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
$result = mysqli_query($conn, "SELECT * FROM users 
                       WHERE username=$matches[0]");
}
else 
{
echo "username 输入异常";
}

假如我们不曾做输入信息的过滤工作，那么现实情况可能发生什么呢？

// 设定$name 中插入了我们不需要的SQL语句
$name = "Qadir'; DELETE FROM users;";
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

以上语句会在查询结束之后，立即删掉所有的数据，这无疑是一场灾难，为了防止以上情况发生，我们需要做的事：

永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和注释符双"-"进行转换等。
永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

防止注入

Perl和PHP等可以对用户输入的数据进行转义从而来防止SQL注入。

导入导出数据

-- 需要设置权限secure_file_priv
mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';
    
-- 导出SQL格式的数据到文件（需要权限）   
$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

-- 导入数据：实际上就是进行数据的建表，插入等等，相当于数据库从无到有。

source 方式
mysql 方式

函数

字符串、数字、日期、高级

运算符

比较运算符

运算符	意义
BETWEEN	在两值之间: `>= min && <= max`
`NOT BETWEEN`	不在两值之间
`IN`	在集合中
`NOT IN`	不在集合中
`<=>`	严格比较两个NULL值是否相等:两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
`LIKE`	模糊匹配
`REGEXP 或 RLIKE`	正则式匹配
`IS NULL`	为空
`IS NOT NULL`	不为空
`=`	只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=

逻辑运算符

与、或、非、亦或

位运算符

alike Java

`COLLATE`

collate是用来排序的规则。对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。另外，$mysql$建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

mysql> show collation;
+-----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                   | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+-----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin                | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci         | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| ascii_bin                   | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
| ascii_general_ci            | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
| big5_bin                    | big5     |  84 |         | Yes      |       1 | PAD SPACE     |
| big5_chinese_ci             | big5     |   1 | Yes     | Yes      |       1 | PAD SPACE     |
| binary                      | binary   |  63 | Yes     | Yes      |       1 | NO PAD        |
| cp1250_bin                  | cp1250   |  66 |         | Yes      |       1 | PAD SPACE     |
| cp1250_croatian_ci          | cp1250   |  44 |         | Yes      |       1 | PAD SPACE     |
| cp1250_czech_cs             | cp1250   |  34 |         | Yes      |       2 | PAD SPACE     |
| cp1250_general_ci           | cp1250   |  26 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1250_polish_ci            | cp1250   |  99 |         | Yes      |       1 | PAD SPACE     |
| cp1251_bin                  | cp1251   |  50 |         | Yes      |       1 | PAD SPACE     |
| cp1251_bulgarian_ci         | cp1251   |  14 |         | Yes      |       1 | PAD SPACE     |
| cp1251_general_ci           | cp1251   |  51 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1251_general_cs           | cp1251   |  52 |         | Yes      |       1 | PAD SPACE     |
| cp1251_ukrainian_ci         | cp1251   |  23 |         | Yes      |       1 | PAD SPACE     |
| cp1256_bin                  | cp1256   |  67 |         | Yes      |       1 | PAD SPACE     |
| cp1256_general_ci           | cp1256   |  57 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1257_bin                  | cp1257   |  58 |         | Yes      |       1 | PAD SPACE     |
| cp1257_general_ci           | cp1257   |  59 | Yes     | Yes      |       1 | PAD SPACE     |
| cp1257_lithuanian_ci        | cp1257   |  29 |         | Yes      |       1 | PAD SPACE     |
| cp850_bin                   | cp850    |  80 |         | Yes      |       1 | PAD SPACE     |
| cp850_general_ci            | cp850    |   4 | Yes     | Yes      |       1 | PAD SPACE     |
| cp852_bin                   | cp852    |  81 |         | Yes      |       1 | PAD SPACE     |
| cp852_general_ci            | cp852    |  40 | Yes     | Yes      |       1 | PAD SPACE     |
| cp866_bin                   | cp866    |  68 |         | Yes      |       1 | PAD SPACE     |
| cp866_general_ci            | cp866    |  36 | Yes     | Yes      |       1 | PAD SPACE     |
| cp932_bin                   | cp932    |  96 |         | Yes      |       1 | PAD SPACE     |
| cp932_japanese_ci           | cp932    |  95 | Yes     | Yes      |       1 | PAD SPACE     |
| dec8_bin                    | dec8     |  69 |         | Yes      |       1 | PAD SPACE     |
| dec8_swedish_ci             | dec8     |   3 | Yes     | Yes      |       1 | PAD SPACE     |
| eucjpms_bin                 | eucjpms  |  98 |         | Yes      |       1 | PAD SPACE     |
| eucjpms_japanese_ci         | eucjpms  |  97 | Yes     | Yes      |       1 | PAD SPACE     |
| euckr_bin                   | euckr    |  85 |         | Yes      |       1 | PAD SPACE     |
| euckr_korean_ci             | euckr    |  19 | Yes     | Yes      |       1 | PAD SPACE     |
| gb18030_bin                 | gb18030  | 249 |         | Yes      |       1 | PAD SPACE     |
| gb18030_chinese_ci          | gb18030  | 248 | Yes     | Yes      |       2 | PAD SPACE     |
| gb18030_unicode_520_ci      | gb18030  | 250 |         | Yes      |       8 | PAD SPACE     |
| gb2312_bin                  | gb2312   |  86 |         | Yes      |       1 | PAD SPACE     |
| gb2312_chinese_ci           | gb2312   |  24 | Yes     | Yes      |       1 | PAD SPACE     |
| gbk_bin                     | gbk      |  87 |         | Yes      |       1 | PAD SPACE     |
| gbk_chinese_ci              | gbk      |  28 | Yes     | Yes      |       1 | PAD SPACE     |
| geostd8_bin                 | geostd8  |  93 |         | Yes      |       1 | PAD SPACE     |
| geostd8_general_ci          | geostd8  |  92 | Yes     | Yes      |       1 | PAD SPACE     |
| greek_bin                   | greek    |  70 |         | Yes      |       1 | PAD SPACE     |
| greek_general_ci            | greek    |  25 | Yes     | Yes      |       1 | PAD SPACE     |
| hebrew_bin                  | hebrew   |  71 |         | Yes      |       1 | PAD SPACE     |
| hebrew_general_ci           | hebrew   |  16 | Yes     | Yes      |       1 | PAD SPACE     |
| hp8_bin                     | hp8      |  72 |         | Yes      |       1 | PAD SPACE     |
| hp8_english_ci              | hp8      |   6 | Yes     | Yes      |       1 | PAD SPACE     |
| keybcs2_bin                 | keybcs2  |  73 |         | Yes      |       1 | PAD SPACE     |
| keybcs2_general_ci          | keybcs2  |  37 | Yes     | Yes      |       1 | PAD SPACE     |
| koi8r_bin                   | koi8r    |  74 |         | Yes      |       1 | PAD SPACE     |
| koi8r_general_ci            | koi8r    |   7 | Yes     | Yes      |       1 | PAD SPACE     |
| koi8u_bin                   | koi8u    |  75 |         | Yes      |       1 | PAD SPACE     |
| koi8u_general_ci            | koi8u    |  22 | Yes     | Yes      |       1 | PAD SPACE     |
| latin1_bin                  | latin1   |  47 |         | Yes      |       1 | PAD SPACE     |
| latin1_danish_ci            | latin1   |  15 |         | Yes      |       1 | PAD SPACE     |
| latin1_general_ci           | latin1   |  48 |         | Yes      |       1 | PAD SPACE     |
| latin1_general_cs           | latin1   |  49 |         | Yes      |       1 | PAD SPACE     |
| latin1_german1_ci           | latin1   |   5 |         | Yes      |       1 | PAD SPACE     |
| latin1_german2_ci           | latin1   |  31 |         | Yes      |       2 | PAD SPACE     |
| latin1_spanish_ci           | latin1   |  94 |         | Yes      |       1 | PAD SPACE     |
| latin1_swedish_ci           | latin1   |   8 | Yes     | Yes      |       1 | PAD SPACE     |
| latin2_bin                  | latin2   |  77 |         | Yes      |       1 | PAD SPACE     |
| latin2_croatian_ci          | latin2   |  27 |         | Yes      |       1 | PAD SPACE     |
| latin2_czech_cs             | latin2   |   2 |         | Yes      |       4 | PAD SPACE     |
| latin2_general_ci           | latin2   |   9 | Yes     | Yes      |       1 | PAD SPACE     |
| latin2_hungarian_ci         | latin2   |  21 |         | Yes      |       1 | PAD SPACE     |
| latin5_bin                  | latin5   |  78 |         | Yes      |       1 | PAD SPACE     |
| latin5_turkish_ci           | latin5   |  30 | Yes     | Yes      |       1 | PAD SPACE     |
| latin7_bin                  | latin7   |  79 |         | Yes      |       1 | PAD SPACE     |
| latin7_estonian_cs          | latin7   |  20 |         | Yes      |       1 | PAD SPACE     |
| latin7_general_ci           | latin7   |  41 | Yes     | Yes      |       1 | PAD SPACE     |
| latin7_general_cs           | latin7   |  42 |         | Yes      |       1 | PAD SPACE     |
| macce_bin                   | macce    |  43 |         | Yes      |       1 | PAD SPACE     |
| macce_general_ci            | macce    |  38 | Yes     | Yes      |       1 | PAD SPACE     |
| macroman_bin                | macroman |  53 |         | Yes      |       1 | PAD SPACE     |
| macroman_general_ci         | macroman |  39 | Yes     | Yes      |       1 | PAD SPACE     |
| sjis_bin                    | sjis     |  88 |         | Yes      |       1 | PAD SPACE     |
| sjis_japanese_ci            | sjis     |  13 | Yes     | Yes      |       1 | PAD SPACE     |
| swe7_bin                    | swe7     |  82 |         | Yes      |       1 | PAD SPACE     |
| swe7_swedish_ci             | swe7     |  10 | Yes     | Yes      |       1 | PAD SPACE     |
| tis620_bin                  | tis620   |  89 |         | Yes      |       1 | PAD SPACE     |
| tis620_thai_ci              | tis620   |  18 | Yes     | Yes      |       4 | PAD SPACE     |
| ucs2_bin                    | ucs2     |  90 |         | Yes      |       1 | PAD SPACE     |
| ucs2_croatian_ci            | ucs2     | 149 |         | Yes      |       8 | PAD SPACE     |
| ucs2_czech_ci               | ucs2     | 138 |         | Yes      |       8 | PAD SPACE     |
| ucs2_danish_ci              | ucs2     | 139 |         | Yes      |       8 | PAD SPACE     |
| ucs2_esperanto_ci           | ucs2     | 145 |         | Yes      |       8 | PAD SPACE     |
| ucs2_estonian_ci            | ucs2     | 134 |         | Yes      |       8 | PAD SPACE     |
| ucs2_general_ci             | ucs2     |  35 | Yes     | Yes      |       1 | PAD SPACE     |
| ucs2_general_mysql500_ci    | ucs2     | 159 |         | Yes      |       1 | PAD SPACE     |
| ucs2_german2_ci             | ucs2     | 148 |         | Yes      |       8 | PAD SPACE     |
| ucs2_hungarian_ci           | ucs2     | 146 |         | Yes      |       8 | PAD SPACE     |
| ucs2_icelandic_ci           | ucs2     | 129 |         | Yes      |       8 | PAD SPACE     |
| ucs2_latvian_ci             | ucs2     | 130 |         | Yes      |       8 | PAD SPACE     |
| ucs2_lithuanian_ci          | ucs2     | 140 |         | Yes      |       8 | PAD SPACE     |
| ucs2_persian_ci             | ucs2     | 144 |         | Yes      |       8 | PAD SPACE     |
| ucs2_polish_ci              | ucs2     | 133 |         | Yes      |       8 | PAD SPACE     |
| ucs2_romanian_ci            | ucs2     | 131 |         | Yes      |       8 | PAD SPACE     |
| ucs2_roman_ci               | ucs2     | 143 |         | Yes      |       8 | PAD SPACE     |
| ucs2_sinhala_ci             | ucs2     | 147 |         | Yes      |       8 | PAD SPACE     |
| ucs2_slovak_ci              | ucs2     | 141 |         | Yes      |       8 | PAD SPACE     |
| ucs2_slovenian_ci           | ucs2     | 132 |         | Yes      |       8 | PAD SPACE     |
| ucs2_spanish2_ci            | ucs2     | 142 |         | Yes      |       8 | PAD SPACE     |
| ucs2_spanish_ci             | ucs2     | 135 |         | Yes      |       8 | PAD SPACE     |
| ucs2_swedish_ci             | ucs2     | 136 |         | Yes      |       8 | PAD SPACE     |
| ucs2_turkish_ci             | ucs2     | 137 |         | Yes      |       8 | PAD SPACE     |
| ucs2_unicode_520_ci         | ucs2     | 150 |         | Yes      |       8 | PAD SPACE     |
| ucs2_unicode_ci             | ucs2     | 128 |         | Yes      |       8 | PAD SPACE     |
| ucs2_vietnamese_ci          | ucs2     | 151 |         | Yes      |       8 | PAD SPACE     |
| ujis_bin                    | ujis     |  91 |         | Yes      |       1 | PAD SPACE     |
| ujis_japanese_ci            | ujis     |  12 | Yes     | Yes      |       1 | PAD SPACE     |
| utf16le_bin                 | utf16le  |  62 |         | Yes      |       1 | PAD SPACE     |
| utf16le_general_ci          | utf16le  |  56 | Yes     | Yes      |       1 | PAD SPACE     |
| utf16_bin                   | utf16    |  55 |         | Yes      |       1 | PAD SPACE     |
| utf16_croatian_ci           | utf16    | 122 |         | Yes      |       8 | PAD SPACE     |
| utf16_czech_ci              | utf16    | 111 |         | Yes      |       8 | PAD SPACE     |
| utf16_danish_ci             | utf16    | 112 |         | Yes      |       8 | PAD SPACE     |
| utf16_esperanto_ci          | utf16    | 118 |         | Yes      |       8 | PAD SPACE     |
| utf16_estonian_ci           | utf16    | 107 |         | Yes      |       8 | PAD SPACE     |
| utf16_general_ci            | utf16    |  54 | Yes     | Yes      |       1 | PAD SPACE     |
| utf16_german2_ci            | utf16    | 121 |         | Yes      |       8 | PAD SPACE     |
| utf16_hungarian_ci          | utf16    | 119 |         | Yes      |       8 | PAD SPACE     |
| utf16_icelandic_ci          | utf16    | 102 |         | Yes      |       8 | PAD SPACE     |
| utf16_latvian_ci            | utf16    | 103 |         | Yes      |       8 | PAD SPACE     |
| utf16_lithuanian_ci         | utf16    | 113 |         | Yes      |       8 | PAD SPACE     |
| utf16_persian_ci            | utf16    | 117 |         | Yes      |       8 | PAD SPACE     |
| utf16_polish_ci             | utf16    | 106 |         | Yes      |       8 | PAD SPACE     |
| utf16_romanian_ci           | utf16    | 104 |         | Yes      |       8 | PAD SPACE     |
| utf16_roman_ci              | utf16    | 116 |         | Yes      |       8 | PAD SPACE     |
| utf16_sinhala_ci            | utf16    | 120 |         | Yes      |       8 | PAD SPACE     |
| utf16_slovak_ci             | utf16    | 114 |         | Yes      |       8 | PAD SPACE     |
| utf16_slovenian_ci          | utf16    | 105 |         | Yes      |       8 | PAD SPACE     |
| utf16_spanish2_ci           | utf16    | 115 |         | Yes      |       8 | PAD SPACE     |
| utf16_spanish_ci            | utf16    | 108 |         | Yes      |       8 | PAD SPACE     |
| utf16_swedish_ci            | utf16    | 109 |         | Yes      |       8 | PAD SPACE     |
| utf16_turkish_ci            | utf16    | 110 |         | Yes      |       8 | PAD SPACE     |
| utf16_unicode_520_ci        | utf16    | 123 |         | Yes      |       8 | PAD SPACE     |
| utf16_unicode_ci            | utf16    | 101 |         | Yes      |       8 | PAD SPACE     |
| utf16_vietnamese_ci         | utf16    | 124 |         | Yes      |       8 | PAD SPACE     |
| utf32_bin                   | utf32    |  61 |         | Yes      |       1 | PAD SPACE     |
| utf32_croatian_ci           | utf32    | 181 |         | Yes      |       8 | PAD SPACE     |
| utf32_czech_ci              | utf32    | 170 |         | Yes      |       8 | PAD SPACE     |
| utf32_danish_ci             | utf32    | 171 |         | Yes      |       8 | PAD SPACE     |
| utf32_esperanto_ci          | utf32    | 177 |         | Yes      |       8 | PAD SPACE     |
| utf32_estonian_ci           | utf32    | 166 |         | Yes      |       8 | PAD SPACE     |
| utf32_general_ci            | utf32    |  60 | Yes     | Yes      |       1 | PAD SPACE     |
| utf32_german2_ci            | utf32    | 180 |         | Yes      |       8 | PAD SPACE     |
| utf32_hungarian_ci          | utf32    | 178 |         | Yes      |       8 | PAD SPACE     |
| utf32_icelandic_ci          | utf32    | 161 |         | Yes      |       8 | PAD SPACE     |
| utf32_latvian_ci            | utf32    | 162 |         | Yes      |       8 | PAD SPACE     |
| utf32_lithuanian_ci         | utf32    | 172 |         | Yes      |       8 | PAD SPACE     |
| utf32_persian_ci            | utf32    | 176 |         | Yes      |       8 | PAD SPACE     |
| utf32_polish_ci             | utf32    | 165 |         | Yes      |       8 | PAD SPACE     |
| utf32_romanian_ci           | utf32    | 163 |         | Yes      |       8 | PAD SPACE     |
| utf32_roman_ci              | utf32    | 175 |         | Yes      |       8 | PAD SPACE     |
| utf32_sinhala_ci            | utf32    | 179 |         | Yes      |       8 | PAD SPACE     |
| utf32_slovak_ci             | utf32    | 173 |         | Yes      |       8 | PAD SPACE     |
| utf32_slovenian_ci          | utf32    | 164 |         | Yes      |       8 | PAD SPACE     |
| utf32_spanish2_ci           | utf32    | 174 |         | Yes      |       8 | PAD SPACE     |
| utf32_spanish_ci            | utf32    | 167 |         | Yes      |       8 | PAD SPACE     |
| utf32_swedish_ci            | utf32    | 168 |         | Yes      |       8 | PAD SPACE     |
| utf32_turkish_ci            | utf32    | 169 |         | Yes      |       8 | PAD SPACE     |
| utf32_unicode_520_ci        | utf32    | 182 |         | Yes      |       8 | PAD SPACE     |
| utf32_unicode_ci            | utf32    | 160 |         | Yes      |       8 | PAD SPACE     |
| utf32_vietnamese_ci         | utf32    | 183 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_bin                 | utf8mb3  |  83 |         | Yes      |       1 | PAD SPACE     |
| utf8mb3_croatian_ci         | utf8mb3  | 213 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_czech_ci            | utf8mb3  | 202 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_danish_ci           | utf8mb3  | 203 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_esperanto_ci        | utf8mb3  | 209 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_estonian_ci         | utf8mb3  | 198 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_general_ci          | utf8mb3  |  33 | Yes     | Yes      |       1 | PAD SPACE     |
| utf8mb3_general_mysql500_ci | utf8mb3  | 223 |         | Yes      |       1 | PAD SPACE     |
| utf8mb3_german2_ci          | utf8mb3  | 212 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_hungarian_ci        | utf8mb3  | 210 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_icelandic_ci        | utf8mb3  | 193 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_latvian_ci          | utf8mb3  | 194 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_lithuanian_ci       | utf8mb3  | 204 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_persian_ci          | utf8mb3  | 208 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_polish_ci           | utf8mb3  | 197 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_romanian_ci         | utf8mb3  | 195 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_roman_ci            | utf8mb3  | 207 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_sinhala_ci          | utf8mb3  | 211 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_slovak_ci           | utf8mb3  | 205 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_slovenian_ci        | utf8mb3  | 196 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_spanish2_ci         | utf8mb3  | 206 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_spanish_ci          | utf8mb3  | 199 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_swedish_ci          | utf8mb3  | 200 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_tolower_ci          | utf8mb3  |  76 |         | Yes      |       1 | PAD SPACE     |
| utf8mb3_turkish_ci          | utf8mb3  | 201 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_unicode_520_ci      | utf8mb3  | 214 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_unicode_ci          | utf8mb3  | 192 |         | Yes      |       8 | PAD SPACE     |
| utf8mb3_vietnamese_ci       | utf8mb3  | 215 |         | Yes      |       8 | PAD SPACE     |
===============================================================================================
| utf8mb4_0900_ai_ci          | utf8mb4  | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci          | utf8mb4  | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs          | utf8mb4  | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin            | utf8mb4  | 309 |         | Yes      |       1 | NO PAD        |
| utf8mb4_bg_0900_ai_ci       | utf8mb4  | 318 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bg_0900_as_cs       | utf8mb4  | 319 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bin                 | utf8mb4  |  46 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_bs_0900_ai_ci       | utf8mb4  | 316 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bs_0900_as_cs       | utf8mb4  | 317 |         | Yes      |       0 | NO PAD        |
| utf8mb4_croatian_ci         | utf8mb4  | 245 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_cs_0900_ai_ci       | utf8mb4  | 266 |         | Yes      |       0 | NO PAD        |
| utf8mb4_cs_0900_as_cs       | utf8mb4  | 289 |         | Yes      |       0 | NO PAD        |
| utf8mb4_czech_ci            | utf8mb4  | 234 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_danish_ci           | utf8mb4  | 235 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_da_0900_ai_ci       | utf8mb4  | 267 |         | Yes      |       0 | NO PAD        |
| utf8mb4_da_0900_as_cs       | utf8mb4  | 290 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_ai_ci    | utf8mb4  | 256 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_as_cs    | utf8mb4  | 279 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_ai_ci       | utf8mb4  | 273 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_as_cs       | utf8mb4  | 296 |         | Yes      |       0 | NO PAD        |
| utf8mb4_esperanto_ci        | utf8mb4  | 241 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_estonian_ci         | utf8mb4  | 230 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_es_0900_ai_ci       | utf8mb4  | 263 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_0900_as_cs       | utf8mb4  | 286 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_ai_ci  | utf8mb4  | 270 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_as_cs  | utf8mb4  | 293 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_ai_ci       | utf8mb4  | 262 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_as_cs       | utf8mb4  | 285 |         | Yes      |       0 | NO PAD        |
| utf8mb4_general_ci          | utf8mb4  |  45 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_german2_ci          | utf8mb4  | 244 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_gl_0900_ai_ci       | utf8mb4  | 320 |         | Yes      |       0 | NO PAD        |
| utf8mb4_gl_0900_as_cs       | utf8mb4  | 321 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hr_0900_ai_ci       | utf8mb4  | 275 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hr_0900_as_cs       | utf8mb4  | 298 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hungarian_ci        | utf8mb4  | 242 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_hu_0900_ai_ci       | utf8mb4  | 274 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hu_0900_as_cs       | utf8mb4  | 297 |         | Yes      |       0 | NO PAD        |
| utf8mb4_icelandic_ci        | utf8mb4  | 225 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_is_0900_ai_ci       | utf8mb4  | 257 |         | Yes      |       0 | NO PAD        |
| utf8mb4_is_0900_as_cs       | utf8mb4  | 280 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs       | utf8mb4  | 303 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs_ks    | utf8mb4  | 304 |         | Yes      |      24 | NO PAD        |
| utf8mb4_latvian_ci          | utf8mb4  | 226 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_la_0900_ai_ci       | utf8mb4  | 271 |         | Yes      |       0 | NO PAD        |
| utf8mb4_la_0900_as_cs       | utf8mb4  | 294 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lithuanian_ci       | utf8mb4  | 236 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_lt_0900_ai_ci       | utf8mb4  | 268 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lt_0900_as_cs       | utf8mb4  | 291 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_ai_ci       | utf8mb4  | 258 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_as_cs       | utf8mb4  | 281 |         | Yes      |       0 | NO PAD        |
| utf8mb4_mn_cyrl_0900_ai_ci  | utf8mb4  | 322 |         | Yes      |       0 | NO PAD        |
| utf8mb4_mn_cyrl_0900_as_cs  | utf8mb4  | 323 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nb_0900_ai_ci       | utf8mb4  | 310 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nb_0900_as_cs       | utf8mb4  | 311 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nn_0900_ai_ci       | utf8mb4  | 312 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nn_0900_as_cs       | utf8mb4  | 313 |         | Yes      |       0 | NO PAD        |
| utf8mb4_persian_ci          | utf8mb4  | 240 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_pl_0900_ai_ci       | utf8mb4  | 261 |         | Yes      |       0 | NO PAD        |
| utf8mb4_pl_0900_as_cs       | utf8mb4  | 284 |         | Yes      |       0 | NO PAD        |
| utf8mb4_polish_ci           | utf8mb4  | 229 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_romanian_ci         | utf8mb4  | 227 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_roman_ci            | utf8mb4  | 239 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_ro_0900_ai_ci       | utf8mb4  | 259 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ro_0900_as_cs       | utf8mb4  | 282 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_ai_ci       | utf8mb4  | 306 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_as_cs       | utf8mb4  | 307 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sinhala_ci          | utf8mb4  | 243 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sk_0900_ai_ci       | utf8mb4  | 269 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sk_0900_as_cs       | utf8mb4  | 292 |         | Yes      |       0 | NO PAD        |
| utf8mb4_slovak_ci           | utf8mb4  | 237 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_slovenian_ci        | utf8mb4  | 228 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sl_0900_ai_ci       | utf8mb4  | 260 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sl_0900_as_cs       | utf8mb4  | 283 |         | Yes      |       0 | NO PAD        |
| utf8mb4_spanish2_ci         | utf8mb4  | 238 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_spanish_ci          | utf8mb4  | 231 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sr_latn_0900_ai_ci  | utf8mb4  | 314 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sr_latn_0900_as_cs  | utf8mb4  | 315 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sv_0900_ai_ci       | utf8mb4  | 264 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sv_0900_as_cs       | utf8mb4  | 287 |         | Yes      |       0 | NO PAD        |
| utf8mb4_swedish_ci          | utf8mb4  | 232 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_tr_0900_ai_ci       | utf8mb4  | 265 |         | Yes      |       0 | NO PAD        |
| utf8mb4_tr_0900_as_cs       | utf8mb4  | 288 |         | Yes      |       0 | NO PAD        |
| utf8mb4_turkish_ci          | utf8mb4  | 233 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_520_ci      | utf8mb4  | 246 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_ci          | utf8mb4  | 224 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vietnamese_ci       | utf8mb4  | 247 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vi_0900_ai_ci       | utf8mb4  | 277 |         | Yes      |       0 | NO PAD        |
| utf8mb4_vi_0900_as_cs       | utf8mb4  | 300 |         | Yes      |       0 | NO PAD        |
| utf8mb4_zh_0900_as_cs       | utf8mb4  | 308 |         | Yes      |       0 | NO PAD        |
+-----------------------------+----------+-----+---------+----------+---------+---------------+

以上字段的collation其格式意义是：Encoding+排序准则算法+ai(as)+ci(cs).

很多COLLATE都带有_ci字样，这是$Case\_Insensitive$的缩写，即大小写无关，也就是说"A"和"a"在排序和比较的时候是一视同仁的。select * from table1 where field1="a"同样可以把field1为"A"的值选出来。与此同时，对于那些_cs后缀的COLLATE，则是$Case\_Sensitive$，即大小写敏感的。ai表示$accent\_insensitive$（发音无关），例如$\ e, è, é, ê , ë\ $是一视同仁的。

标签：ci,utf8mb4,SPACE,NO,库表,PAD,Yes
From： https://www.cnblogs.com/marvin-Hua-manlou/p/17012816.html

库表

规约

字符串

索引问题

数值类型

字符串类型

MYSQL `LIKE`

`GROUP BY` 与 `WITH ROLLUP`

排序 `ORDER BY`

连接语句

正则表达式

`ALTER`关键字

`SHOW STATUS`

`INDEX索引`

临时表`TEMPORARY TABLE`

`TABLE CLONE`

SQL元数据

`AUTO_INCREMENT`序列

删除`PRIMARY KEY`

数据重复处理

采用`primary key、unique`

统计重复的数据

过滤重复数据`DISTINCT`

删除重复数据

`SQL`注入

SQL注入方式

防止注入

导入导出数据

函数

运算符

比较运算符

逻辑运算符

位运算符

`COLLATE`

相关文章

赞助商

阅读排行

库表

规约

字符串

索引问题

数值类型

字符串类型

MYSQL LIKE

GROUP BY 与 WITH ROLLUP

排序 ORDER BY

连接语句

正则表达式

ALTER关键字

SHOW STATUS

INDEX索引

临时表TEMPORARY TABLE

TABLE CLONE

SQL元数据

AUTO_INCREMENT序列

删除PRIMARY KEY

数据重复处理

采用primary key、unique

统计重复的数据

过滤重复数据DISTINCT

删除重复数据

SQL注入

SQL注入方式

防止注入

导入导出数据

函数

运算符

比较运算符

逻辑运算符

位运算符

COLLATE

相关文章

赞助商

阅读排行

MYSQL `LIKE`

`GROUP BY` 与 `WITH ROLLUP`

排序 `ORDER BY`

`ALTER`关键字

`SHOW STATUS`

`INDEX索引`

临时表`TEMPORARY TABLE`

`TABLE CLONE`

`AUTO_INCREMENT`序列

删除`PRIMARY KEY`

采用`primary key、unique`

过滤重复数据`DISTINCT`

`SQL`注入

`COLLATE`