MySQL 字符集和建表(临时表，已有表建表)

查看MySQL里所有的字符集

root@tennis 16:34  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 |
| 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 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

MySQL默认使用 latin1字符集

查看MySQL使用的字符集

root@tennis 17:15  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配置文件里指定了字符集

[root@master ~]# cat /etc/my.cnf
[mysqld_safe]

[client]
socket=/data/mysql/mysql.sock

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8  # 指定字符集 utf8

[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>

查看Linux里的字符集

[root@master ~]# locale
LANG=zh_CN.UTF-8
LC_CTYPE="zh_CN.UTF-8"
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER="zh_CN.UTF-8"
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT="zh_CN.UTF-8"
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=

查看tennis库的字符集

root@tennis 17:17  mysql>show create database tennis;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| tennis   | CREATE DATABASE `tennis` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

创建test库指定utf8mb4字符集

root@tennis 17:19  mysql>create database test default charset=utf8mb4;
Query OK, 1 row affected (0.01 sec)

root@tennis 17:24  mysql>show create database test;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

root@tennis 17:24  mysql>use test;
Database changed
root@test 17:26  mysql>show tables;
Empty set (0.00 sec)

root@test 17:26  mysql>create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

root@test 17:26  mysql>show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改test库的字符集

root@test 17:26  mysql>alter database test default character set utf8;
Query OK, 1 row affected (0.00 sec)

root@test 17:29  mysql>show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

使用 if not exists ，创建表存在时不输出错误信息。 

root@test 17:29  mysql>create table t1(id int,name varchar(20));
ERROR 1050 (42S01): Table 't1' already exists
root@test 17:31  mysql>create table if not exists  t1(id int,name varchar(20));
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@test 17:31  mysql>show warnings;
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Note  | 1050 | Table 't1' already exists |
+-------+------+---------------------------+
1 row in set (0.00 sec)

表和库名区分大小写，其他的命令，列名，数据类型，约束等都不区分大小写

root@test 17:34  mysql>insert into t1 values(1,'han'),(2,'chao');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@test 17:35  mysql>select ID,NAME from t1;
+------+------+
| ID   | NAME |
+------+------+
|    1 | han  |
|    2 | chao |
+------+------+
2 rows in set (0.00 sec)

root@test 17:35  mysql>select id,name from t1;
+------+------+
| id   | name |
+------+------+
|    1 | han  |
|    2 | chao |
+------+------+
2 rows in set (0.00 sec)

root@test 17:36  mysql>select id,name from t1;
+------+------+
| id   | name |
+------+------+
|    1 | han  |
|    2 | chao |
+------+------+
2 rows in set (0.00 sec)

root@test 17:36  mysql>select id,name from T1;
ERROR 1146 (42S02): Table 'test.T1' doesn't exist

root@test 17:36  mysql>use TEST;
ERROR 1049 (42000): Unknown database 'TEST'

---

创建临时表

root@song 10:22  mysql>create temporary table sanchuang(id int);
Query OK, 0 rows affected (0.00 sec)

root@song 10:23  mysql>show tables;
+----------------+
| Tables_in_song |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

root@song 10:23  mysql>insert into sanchuang(id) values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@song 10:24  mysql>select * from sanchuang;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

root@song 10:25  mysql>exit
Bye

重新进入MySQL

root@song 10:32  mysql>select * from sanchuang;
ERROR 1146 (42S02): Table 'song.sanchuang' doesn't exist

临时表的特点：

1. show tables 是看不到的，只能当前的终端用户可以使用，其他终端的用户看不到，MySQL给每个登录的用户创建了一个session
2. 临时表是隔离的，当用户退出MySQL的时候，临时表会被删除，临时表放在内存里。

---

根据已有表建表

root@hunan 01:09  mysql>create table hunan.user like mysql.user;
Query OK, 0 rows affected (0.01 sec)

root@hunan 01:12  mysql>show tables;
+-----------------+
| Tables_in_hunan |
+-----------------+
| sc_student      |
| user            |
+-----------------+
2 rows in set (0.00 sec)

root@hunan 01:13  mysql>select user,host from user;
Empty set (0.00 sec)

root@hunan 01:13  mysql>create table user2 as select user,host from mysql.user;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@hunan 01:14  mysql>desc user2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| user  | char(32) | NO   |     |         |       |
| host  | char(60) | NO   |     |         |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@hunan 01:14  mysql>select user,host from user2;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| hanwl         | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

---

当使用zerofill时，默认会自动加unsigned(无符号)属性，使用unsigned属性后，数值范围是原值的2倍（有符号为-128~+127，无符号为0~256）

int(4) zerofill 表示当数值宽度小于4位的时候在数字前面填充0,如果不显示指定宽度则默认为 int(11)，zerofill默认为int(10)。

root@sc 09:56  mysql>create table width(id int(4) zerofill primary key,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

root@sc 09:58  mysql>desc width;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(4) unsigned zerofill | NO   | PRI | NULL    |       |
| name  | varchar(10)              | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

root@sc 09:58  mysql>insert into width(id,name)values(1,'cali'),(2,'lianglinag');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@sc 09:59  mysql>select * from width;
+------+------------+
| id   | name       |
+------+------------+
| 0001 | cali       |
| 0002 | lianglinag |
+------+------------+
2 rows in set (0.00 sec)

root@sc 09:59  mysql>insert into width(id,name)values(1,'cali'),(2,'lianglinag');
ERROR 1062 (23000): Duplicate entry '0001' for key 'PRIMARY'

root@sc 10:00  mysql>insert into width(id,name)values(3,'cali'),(4,'lianglinag');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@sc 10:06  mysql>select * from width;
+------+------------+
| id   | name       |
+------+------------+
| 0001 | cali       |
| 0002 | lianglinag |
| 0003 | cali       |
| 0004 | lianglinag |
+------+------------+
4 rows in set (0.00 sec)

---

auto_increment是用于主键自动增长的，从1开始增长，当你把第一条记录删除时，再插入第二跳数据时，主键值为2。

root@sc 11:02  mysql>select @@auto_increment_offset;
+-------------------------+
| @@auto_increment_offset |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

root@sc 11:15  mysql>select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

root@sc 11:16  mysql>set @@auto_increment_offset = 10;
Query OK, 0 rows affected (0.00 sec)

root@sc 11:16  mysql>select @@auto_increment_offset;
+-------------------------+
| @@auto_increment_offset |
+-------------------------+
|                      10 |
+-------------------------+
1 row in set (0.00 sec)

root@sc 10:06  mysql>create table city_name(id int unsigned auto_increment primary key,name varchar(10) not null);
Query OK, 0 rows affected (0.00 sec)

root@sc 10:10  mysql>desc city_name;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

root@sc 10:11  mysql>insert into city_name values('shan',1),("jiao",2);
ERROR 1366 (HY000): Incorrect integer value: 'shan' for column 'id' at row 1

root@sc 10:10  mysql>insert into city_name(name,id) values('shan',1),("jiao",2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@sc 10:11  mysql>select * from city_name;
+----+------+
| id | name |
+----+------+
|  1 | shan |
|  2 | jiao |
+----+------+
2 rows in set (0.00 sec)

root@sc 10:12  mysql>insert into city_name(name) values('dong');
Query OK, 1 row affected (0.00 sec)

root@sc 10:14  mysql>select * from city_name;
+----+------+
| id | name |
+----+------+
|  1 | shan |
|  2 | jiao |
|  3 | dong |
+----+------+
3 rows in set (0.00 sec)