转载于老男孩教育
20、MySQL内中文数据乱码的原理及如何防止乱码?(可选)1. 网站程序字符集
2. 客户端的字符集
3. 服务器端字符集
4. linux客户端字符集
5. 以上都要统一,否则会出现中文乱码如果编译的时候指定了特定的字符集,则以后创建对应字符集的数据库就不需要指定字符集
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
提示:二进制软件包,安装的数据库字符集默认latinl
21.在把id列设置为主键,在Name字段上创建普通索引
mysql> alter table test add primary key(id);
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | 0 | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
添加索引步骤mysql> alter table test add index index_name(name);
create index index_name on test(name);
22.在字段name后插入手机号字段(shouji),类型char(11)
mysql> alter table test add shouji char(11) after name;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc test;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | 0 | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | YES | MUL | NULL | |
| shouji | char(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
23、所有字段上插入2条记录(自行设定数据)
mysql> insert into test values(4,24,'cyh','604419314'),(5,38,'oldboy','123456');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+------+-----------+-----------+
| id | age | name | shouji |
+----+------+-----------+-----------+
| 1 | NULL | oldgirl | NULL |
| 2 | NULL | 老男孩 | NULL |
| 3 | NULL | etiantian | NULL |
| 4 | 24 | cyh | 604419314 |
| 5 | 38 | oldboy | 123456 |
+----+------+-----------+-----------+
5 rows in set (0.00 sec
24、在手机字段上对前8个字符创建普通索引
mysql> alter table test add index index_shouji(shouji(8));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+------+-----------+-----------+
| id | age | name | shouji |
+----+------+-----------+-----------+
| 1 | NULL | oldgirl | NULL |
| 2 | NULL | 老男孩 | NULL |
| 3 | NULL | etiantian | NULL |
| 4 | 24 | cyh | 604419314 |
| 5 | 38 | oldboy | 123456 |
+----+------+-----------+-----------+
5 rows in set (0.00 sec)
25、查看创建的索引及索引类型等信息
mysql> show index from test\G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: test
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: test
Non_unique: 1
Key_name: index_shouji
Seq_in_index: 1
Column_name: shouji
Collation: A
Cardinality: 5
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:Index_comment:3
rows in set (0.00 sec)
26、删除Name,shouji列的索引
alter table test drop index index_name;
drop index index_shouji on test;
mysql> drop index index_shouji on test;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test\G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec
27、对Name列的前6个字符以及手机列的前8个字符组建联合索引
mysql> alter table test add index index_name_shouji(name(6),shouji(8));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test\G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: test Non_unique: 1
Key_name: index_name_shouji
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 5
Sub_part: 6
Packed: NULL
Null: YES
Index_type: BTREE
Comment:Index_comment:
*************************** 3. row **************************
Table: test
Non_unique: 1
Key_name: index_name_shouji
Seq_in_index: 2
Column_name: shouji
Collation: A Cardinality: 5
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)
28.查询手机号以135开头的,名字为oldboy的记录(此记录要提前插入)
mysql> select * from test where name='cyh' and shouji like '6044%';
+----+------+------+-----------+
| id | age | name | shouji |
+----+------+------+-----------+
| 4 | 24 | cyh | 604419314 |
+----+------+------+-----------+
1 row in set (0.00 sec)
29.查询上述语句的执行计划(是否使用联合索引等)
mysql> explain select * from test where name='cyh' and shouji like '6044%'\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: test
type: rangepossible_keys: index_name_shouji
key: index_name_shouji
key_len: 32
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
30、把test表的引擎改成MyISAM
mysql> SHOW CREATE TABLE TEST\G
*************************** 1. row ***************************
Table: TEST
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL DEFAULT '0',
`age` tinyint(2) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`shouji` char(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name_shouji` (`name`(6),`shouji`(8))
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
修改后====================================
mysql> alter table test ENGINE=MYISAM;
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE TEST\G
*************************** 1. row ***************************
Table: TEST
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL DEFAULT '0',
`age` tinyint(2) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`shouji` char(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name_shouji` (`name`(6),`shouji`(8))
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)