|
Andy Niu Help
1.0.0.0
|
变量 | |
| Mysql内外连接 | |
| MySQL的Embedded模式 | |
| mysql下划线和中划线 | |
| Mysql的两种连接方式 | |
| Mysql默认的数据库 | |
| Mysql有时候为什么要flush | |
| Mysql的有关日志 | |
| mysql表的复制 | |
| 根据已有的记录增加新的记录 | |
| 执行sql文件，添加数据乱码 | |
| 查看字符串的16进制取值 | |
| Sql_Mode | |
| Sql注入 | |
| 分割求总数 | |
| 分割后求第n个元素 | |
| 删除重复记录_保存Id最小的一条 | |
详细描述
变量说明
| mysql下划线和中划线 |
1、通过select或者show variables 可以看到系统变量单词之间的分隔符是下划线，如下：
mysql> show variables like 'event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
1 row in set (0.00 sec)
2、在配置文件 my.cnf中，使用下划线或者中划线都是可以的。如下：
lower_case_table_names=1
default-character-set=utf8
event-scheduler=on
3、另外对于开关变量，比如event-scheduler，使用on或者1也都是可以的。
event-scheduler=1
或者 event-scheduler=on
4、但是在执行命令，使用选项参数的时候，必须使用中划线。
两个中划线是全写方式，选项和取值之间必须有=号或者空格，但是有些必须使用=号，比如密码之类。
单中划线是简写方式，选项和取值之间不能使用=号，可以有空格也可以没有空格，但是有些必须没有空格，比如密码之类。
如下：
mysql --user=root --password=123456
mysql --user root --password=123456
mysql -u root -p123456
mysql -uroot -p123456
| Mysql内外连接 |
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | int(11) | NO | PRI | 0 | | | NAME | varchar(16) | YES | | NULL | | | AGE | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set mysql> desc sc; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | SID | int(11) | YES | | NULL | | | CID | int(11) | YES | MUL | NULL | | | SCORE | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set mysql> select * from student; +-------+----------+-----+ | ID | NAME | AGE | +-------+----------+-----+ | 10001 | Andy | 26 | | 10002 | Bill | 27 | | 10003 | Caroline | 34 | | 10004 | David | 46 | +-------+----------+-----+ 4 rows in set mysql> select * from sc; +----+-------+-----+-------+ | ID | SID | CID | SCORE | +----+-------+-----+-------+ | 1 | 10001 | 101 | 78 | | 2 | 10001 | 102 | 67 | | 3 | 10008 | 103 | 100 | +----+-------+-----+-------+ 3 rows in set ----------------------------------------------------------------------------------------------------------------- 内连接 mysql> select student.*, sc.* from student inner join sc on student.id = sc.sid; +-------+------+-----+----+-------+-----+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+------+-----+----+-------+-----+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10001 | Andy | 26 | 2 | 10001 | 102 | 67 | +-------+------+-----+----+-------+-----+-------+ 2 rows in set 内连接等价于我们平时的自然连接，也就是： mysql> select student.*, sc.* from student,sc where student.id = sc.sid; +-------+------+-----+----+-------+-----+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+------+-----+----+-------+-----+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10001 | Andy | 26 | 2 | 10001 | 102 | 67 | +-------+------+-----+----+-------+-----+-------+ 2 rows in set ----------------------------------------------------------------------------------------------------------------- 左连接 考虑下面的需求，我想列出所有学生对应的成绩，一个学生可能多个成绩，也可能没有成绩。有多个成绩把多个成绩列出来，没有成绩的话，成绩这些字段的值使用NULL填充。怎么解决这个问题？ 使用左连接，student表 left join sc表，如下： mysql> select student.*, sc.* from student left join sc on student.id = sc.sid; +-------+----------+-----+------+-------+------+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+----------+-----+------+-------+------+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10001 | Andy | 26 | 2 | 10001 | 102 | 67 | | 10002 | Bill | 27 | NULL | NULL | NULL | NULL | | 10003 | Caroline | 34 | NULL | NULL | NULL | NULL | | 10004 | David | 46 | NULL | NULL | NULL | NULL | +-------+----------+-----+------+-------+------+-------+ 5 rows in set ----------------------------------------------------------------------------------------------------------------- 右连接 考虑下面的需求，我想列出所有成绩对应的学生，一个成绩有对应的学生，也可能没有对应的学生，比如这个学生开除了。有学生就把学生列出来，没有学生的话，学生这些字段的值使用NULL填充。怎么解决这个问题？ 使用右连接，student表 right join sc表，如下： mysql> select student.*, sc.* from student right join sc on student.id = sc.sid; +-------+------+------+----+-------+-----+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+------+------+----+-------+-----+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10001 | Andy | 26 | 2 | 10001 | 102 | 67 | | NULL | NULL | NULL | 3 | 10008 | 103 | 100 | +-------+------+------+----+-------+-----+-------+ 3 rows in set 根据对称性，A left join B 等价于 B right join A ----------------------------------------------------------------------------------------------------------------- 外连接 左连接也叫左外连接（同理右连接），这里的外连接也叫全外连接。 考虑下面的需求，我想列出所有学生对应的所有成绩，这里存在学生可能没有成绩，成绩也可能没有对应的学生，没有的话，也要列出来，这些字段的值使用NULL填充。 目前，mysql不支持外连接，解决办法是使用union组合查询，把左连接和右连接的结果合并。如下： mysql> select student.*, sc.* from student left join sc on student.id = sc.sid union select student.*, sc.* from student right join sc on student.id = sc.sid; +-------+----------+------+------+-------+------+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+----------+------+------+-------+------+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10001 | Andy | 26 | 2 | 10001 | 102 | 67 | | 10002 | Bill | 27 | NULL | NULL | NULL | NULL | | 10003 | Caroline | 34 | NULL | NULL | NULL | NULL | | 10004 | David | 46 | NULL | NULL | NULL | NULL | | NULL | NULL | NULL | 3 | 10008 | 103 | 100 | +-------+----------+------+------+-------+------+-------+ 6 rows in set 注意：这里的union自动去除了重复行，如果不想去除重复行，使用union all ----------------------------------------------------------------------------------------------------------------- 还有一点需要注意：就是on 之后的条件，如下： mysql> select student.*, sc.* from student left join sc on student.id = sc.sid; +-------+----------+-----+------+-------+------+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+----------+-----+------+-------+------+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10001 | Andy | 26 | 2 | 10001 | 102 | 67 | | 10002 | Bill | 27 | NULL | NULL | NULL | NULL | | 10003 | Caroline | 34 | NULL | NULL | NULL | NULL | | 10004 | David | 46 | NULL | NULL | NULL | NULL | +-------+----------+-----+------+-------+------+-------+ 5 rows in set mysql> select student.*, sc.* from student left join sc on student.id = sc.sid and sc.cid=101; +-------+----------+-----+------+-------+------+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+----------+-----+------+-------+------+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10002 | Bill | 27 | NULL | NULL | NULL | NULL | | 10003 | Caroline | 34 | NULL | NULL | NULL | NULL | | 10004 | David | 46 | NULL | NULL | NULL | NULL | +-------+----------+-----+------+-------+------+-------+ 4 rows in set mysql> select student.*, sc.* from student left join sc on student.id = sc.sid where sc.cid=101; +-------+------+-----+----+-------+-----+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+------+-----+----+-------+-----+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | +-------+------+-----+----+-------+-----+-------+ 1 row in set 这里看出第二个查询和第三个查询的区别，换一种写法就很清楚了。 mysql> select student.*, sc.* from student left join sc on (student.id = sc.sid and sc.cid=101); +-------+----------+-----+------+-------+------+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+----------+-----+------+-------+------+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10002 | Bill | 27 | NULL | NULL | NULL | NULL | | 10003 | Caroline | 34 | NULL | NULL | NULL | NULL | | 10004 | David | 46 | NULL | NULL | NULL | NULL | +-------+----------+-----+------+-------+------+-------+ 4 rows in set mysql> select student.*, sc.* from student left join sc on (student.id = sc.sid) where sc.cid=101; +-------+------+-----+----+-------+-----+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+------+-----+----+-------+-----+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | +-------+------+-----+----+-------+-----+-------+ 1 row in set mysql> select student.*, sc.* from student left join sc on (student.id = sc.sid where sc.cid=101); 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where sc.cid=101)' at line 1 ----------------------------------------------------------------------------------------------------------------- select student.*, sc.* from student left join sc on (student.id = sc.sid and sc.cid=101); 相当于： 1、内部连接 mysql> select student.*, sc.* from student inner join sc on student.id = sc.sid; +-------+------+-----+----+-------+-----+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+------+-----+----+-------+-----+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10001 | Andy | 26 | 2 | 10001 | 102 | 67 | +-------+------+-----+----+-------+-----+-------+ 2 rows in set 2、选择出sc.cid=101，再进行左连接，没有成绩的使用NULL填充 ----------------------------------------------------------------------------------------------------------------- select student.*, sc.* from student left join sc on (student.id = sc.sid) where sc.cid=101; 相当于： 1、左连接 mysql> select student.*, sc.* from student left join sc on student.id = sc.sid; +-------+----------+-----+------+-------+------+-------+ | ID | NAME | AGE | ID | SID | CID | SCORE | +-------+----------+-----+------+-------+------+-------+ | 10001 | Andy | 26 | 1 | 10001 | 101 | 78 | | 10001 | Andy | 26 | 2 | 10001 | 102 | 67 | | 10002 | Bill | 27 | NULL | NULL | NULL | NULL | | 10003 | Caroline | 34 | NULL | NULL | NULL | NULL | | 10004 | David | 46 | NULL | NULL | NULL | NULL | +-------+----------+-----+------+-------+------+-------+ 5 rows in set 2、再选出 sc.cid=101
- 参见
| Mysql有时候为什么要flush |
1、为了性能考虑，mysql服务启动的时候，会把常用的信息从数据库中加载到内存。
这样以后在查询或者操作的时候，直接在内存中操作就行了，大大提高效率。
dmu的做法也是这样的。
2、对于GRANT、REVOKE、SET PASSWORD操作，mysql会更新数据库中对应的表，同时刷新内存中的数据。
但是对于手工修改表的数据，比如insert、update，mysql内存中的数据并不会更新，导致内存和表的数据不一致。
客户端的请求，mysql还是在内存中进行，表现为insert，update操作没有起作用。
3、怎么解决这个问题？
第一种方法是：重启mysql
第二种方法是：进行相应的flush，比如flush privileges，将数据库中的信息刷新到内存中。
4、测试如下：
mysql> drop user niu;
Query OK, 0 rows affected (0.00 sec)
mysql> create user niu;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set password=password('123') where user='niu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
登录mysql -uniu -p123 报错，如下：
[root@localhost ~]# mysql -uniu -p123
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'niu'@'localhost' (using password: YES)
进行flush privileges
登录mysql -uniu -p123成功
使用
mysql> set password for niu=password('456');
Query OK, 0 rows affected (0.00 sec)
登录mysql -uniu -p456成功
| MySQL的Embedded模式 |
1、一般情况下，我们通过CS模式，调用MySQL客户端mysqlclient和MySQL服务交互。
2、但是这种C/S的数据服务不适合于一些轻量级的应用，可以使用MySQL的Embedded模式，相当于以库的方式调用。
3、这两种方式的区别是：
CS模式，客户端链接mysqlclient库，调用mysqlclient的接口，经过网络和mysql服务交互。
Embedded模式，客户端链接mysqld库，调用mysqld的接口，都在在本地操作。
4、CS模式
sudo apt-get install libmysqlclient-dev
g++ -o Embed Embed.cc `mysql_config --include --libs` -std=c++11
5、Embedded模式
sudo apt install libmysqld-dev
g++ -o Embed Embed.cc `mysql_config --include --libmysqld-libs` -std=c++11
6、可以看一下，mysql_config的输出，如下：
niuzibin@ubuntu:~/work/CPP_2/Embed$ mysql_config
Usage: /usr/bin/mysql_config [OPTIONS]
Options:
--cflags [-I/usr/include/mysql -DBIG_JOINS=1 -fno-strict-aliasing -g -DNDEBUG]
--include [-I/usr/include/mysql]
--libs [-L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -ldl]
--libs_r [-L/usr/lib/x86_64-linux-gnu -lmysqlclient_r -lpthread -lz -lm -ldl]
--plugindir [/usr/lib/mysql/plugin]
--socket [/var/run/mysqld/mysqld.sock]
--port [0]
--version [5.5.59]
--libmysqld-libs [-L/usr/lib/x86_64-linux-gnu -lmysqld -lpthread -lz -lm -lwrap -lcrypt -ldl -laio]
--variable=VAR VAR is one of:
pkgincludedir [/usr/include/mysql]
pkglibdir [/usr/lib/x86_64-linux-gnu]
plugindir [/usr/lib/mysql/plugin]
| Mysql的两种连接方式 |
1、mysql是典型的的CS架构，连接mysql服务有两种方式：
TCP/IP套接字和Unix域套接字
2、TCP/IP套接字是MySQL在任何平台都提供的一种连接方式，也是网络中使用最多的一种方式。
这种方式通过TCP/IP建立网络连接，一般情况下客户端在一台服务器上，而MySQL实例在另外一台服务器上，如下：
mysql -h127.0.0.1 -uroot -p123456
使用netstat可以看到TCP连接，在Linux使用tcpdump抓包可以看到交互的数据（在Windows下抓不到环回包）
3、如果mysql客户端和数据库实例在同一台服务器上，可以使用Unix域套接字连接。
注：Unix域套接字其实不是网络协议，mysql客户端和数据库实例之间没有TCP连接。
查看socket文件，如下：
mysql> show variables like 'socket';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| socket | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+
使用UNIX域套接字连接，如下：
mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock
UNIX域套接字比TCP/IP套接字效率更高，mysql默认使用Unix域套接字来连接，下面的方式都是使用Unix域套接字。
mysql -uroot -p123456
mysql -hlocalhost -uroot -p123456
4、通过TCP/IP套接字连接，抓包可以获取交互信息
a、抓包获取的内容如下：
"......select serverid,title from serversmanagement;
.....P....
def.ibp_niu1.serversmanagement.serversmanagement.serverid.SERVERID.?.......B...J....
def.ibp_niu1.serversmanagement.serversmanagement.title.TITLE.!.,................
"......10.VTDU.....11.DMU.....18.VRU......."
b、具体内容，请求是select serverid,title from serversmanagement;
回复包括槽位的信息，以及返回的数据集合。
c、注意：登录的时候，密码字段是加密的。
| Mysql的有关日志 |
1、普通日志
mysql> show variables like 'general%';
+------------------+------------------------------+
| Variable_name | Value |
+------------------+------------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)
2、注意：普通日志，不仅记录select查询语句，还记录insert，update等语句，查看普通日志，如下：
[root@localhost ~]# tail /var/lib/mysql/localhost.log
89 Query show tables
89 Field List a
89 Field List b
89 Field List t1
89 Field List t2
160625 11:28:28 89 Query create table t1(ID int)
160625 11:28:33 89 Query create table t100(ID int)
160625 11:37:25 89 Query insert t100 values(52)
160625 11:37:42 89 Query show variables like 'general%'
160625 11:38:09 89 Query update t100 set id=456
3、慢查询日志
mysql> show global variables like '%_query%';
+------------------------------+-----------------------------------+
| Variable_name | Value |
+------------------------------+-----------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 4.000000 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+------------------------------+-----------------------------------+
6 rows in set (0.01 sec)
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%_query%';
+------------------------------+-----------------------------------+
| Variable_name | Value |
+------------------------------+-----------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 3.000000 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+------------------------------+-----------------------------------+
6 rows in set (0.00 sec)
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
4、查看慢查询日志：
[root@localhost ~]# tail /var/lib/mysql/localhost-slow.log
Time Id Command Argument
# Time: 160625 12:22:23
# User@Host: root[root] @ localhost [] Id: 89
# Query_time: 5.002054 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use niu1;
SET timestamp=1466828543;
select sleep(5);
/usr/sbin/mysqld, Version: 5.6.15 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
| mysql表的复制 |
1、create table xxx as select xxx，创建新表，没有原表的完整约束，会把原表的数据拷贝一份，如下： mysql> desc stu; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | Id | int(9) | NO | PRI | NULL | auto_increment | | Name | varchar(100) | NO | | NULL | | | Age | int(9) | NO | | 0 | | | updatetime | datetime | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set mysql> select * from stu; +----+------+-----+---------------------+ | Id | Name | Age | updatetime | +----+------+-----+---------------------+ | 1 | Andy | 28 | 2015-03-19 15:42:09 | +----+------+-----+---------------------+ 1 row in set mysql> create table stu2 as select * from stu; Query OK, 1 row affected Records: 1 Duplicates: 0 Warnings: 0 mysql> desc stu2; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | Id | int(9) | NO | | 0 | | | Name | varchar(100) | NO | | NULL | | | Age | int(9) | NO | | 0 | | | updatetime | datetime | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 4 rows in set mysql> select * from stu2; +----+------+-----+---------------------+ | Id | Name | Age | updatetime | +----+------+-----+---------------------+ | 1 | Andy | 28 | 2015-03-19 15:42:09 | +----+------+-----+---------------------+ 1 row in set 2、create table xxx like xxx，创建新表，约束和原表相同，只拷贝表结构，没有拷贝表的数据，如下： mysql> desc stu; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | Id | int(9) | NO | PRI | NULL | auto_increment | | Name | varchar(100) | NO | | NULL | | | Age | int(9) | NO | | 0 | | | updatetime | datetime | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set mysql> select * from stu; +----+------+-----+---------------------+ | Id | Name | Age | updatetime | +----+------+-----+---------------------+ | 1 | Andy | 28 | 2015-03-19 15:42:09 | +----+------+-----+---------------------+ 1 row in set mysql> create table stu3 like stu; Query OK, 0 rows affected mysql> desc stu3; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | Id | int(9) | NO | PRI | NULL | auto_increment | | Name | varchar(100) | NO | | NULL | | | Age | int(9) | NO | | 0 | | | updatetime | datetime | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set mysql> select * from stu3; Empty set 3、如果我想拷贝表的结构（约束和原表相同），同时拷贝表的数据，怎么办？ 先create table xxx like xxx，创建表结构，再insert into xxx select xxx 拷贝数据。注意：这里没有as mysql> desc stu; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | Id | int(9) | NO | PRI | NULL | auto_increment | | Name | varchar(100) | NO | | NULL | | | Age | int(9) | NO | | 0 | | | updatetime | datetime | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set mysql> select * from stu; +----+------+-----+---------------------+ | Id | Name | Age | updatetime | +----+------+-----+---------------------+ | 1 | Andy | 28 | 2015-03-19 15:42:09 | +----+------+-----+---------------------+ 1 row in set mysql> create table stu4 like stu; Query OK, 0 rows affected mysql> desc stu4; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | Id | int(9) | NO | PRI | NULL | auto_increment | | Name | varchar(100) | NO | | NULL | | | Age | int(9) | NO | | 0 | | | updatetime | datetime | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set mysql> select * from stu4; Empty set mysql> insert into stu4(name,age,updatetime) select name,age,updatetime from stu; Query OK, 1 row affected Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from stu4; +----+------+-----+---------------------+ | Id | Name | Age | updatetime | +----+------+-----+---------------------+ | 1 | Andy | 28 | 2015-03-19 15:42:09 | +----+------+-----+---------------------+ 1 row in set
| Mysql默认的数据库 |
1、数据库mysql，保存的信息有：
用户，帮助信息，普通日志，慢查询日志，时区有关。
2、数据库information_schema，保存的信息有：
schemata，tables，columns，funcs，procedures，events，partitions,triggers，各种privileges，
全局和会话的status和variables，processlist,innodb的事务和锁
注意：有些表并没有实际的物理文件，而是对应着内存中的一些信息。
3、数据库performance_schema，保存的信息有：
性能统计和分析
| Sql_Mode |
1、Sql Mode（Sql模式），mysql可以运行在不同的Sql Mode下，不同的Sql Mode定义了不同的Sql语法和数据校验。
2、举例来说，
mysql> select @@session.sql_mode;
+-------------------------------------------------------------+
| @@session.sql_mode |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set
mysql> select * from user;
+----+------+----------+------------+
| ID | NAME | PASSWORD | UPDATETIME |
+----+------+----------+------------+
| 1 | Andy | 123456 | NULL |
+----+------+----------+------------+
1 row in set
mysql> update user set updatetime='2015-04-31 15:0:0';
Database changed
Rows matched: 1 Changed: 1 Warnings: 1
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'UPDATETIME' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set
mysql> select * from user;
+----+------+----------+---------------------+
| ID | NAME | PASSWORD | UPDATETIME |
+----+------+----------+---------------------+
| 1 | Andy | 123456 | 0000-00-00 00:00:00 |
+----+------+----------+---------------------+
1 row in set
在ansi模式下，更新一个无效的datetime，mysql给出一个warning，更新的时间为 0000-00-00 00:00:00
3、Sql模式设置为 strict_trans_tables，如下：
mysql> set @@session.sql_mode='strict_trans_tables';
Query OK, 0 rows affected
mysql> update user set updatetime='2015-04-31 15:0:0';
1292 - Incorrect datetime value: '2015-04-31 15:0:0' for column 'UPDATETIME' at row 1
直接报错，error，不允许更新
考虑事务的情况，如下：
mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set name='Bill' where id=1;
Database changed
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set updatetime='2015-04-31 15:0:0';
1292 - Incorrect datetime value: '2015-04-31 15:0:0' for column 'UPDATETIME' at row 1
mysql> commit;
Query OK, 0 rows affected
mysql> select * from user;
+----+------+----------+---------------------+
| ID | NAME | PASSWORD | UPDATETIME |
+----+------+----------+---------------------+
| 1 | Bill | 123456 | 0000-00-00 00:00:00 |
+----+------+----------+---------------------+
1 row in set
出现错误，不影响前面的操作，提交有效的操作。
4、strict_trans_tables比ansi严格，traditional比strict_trans_tables更严格。
5、需要说明的，ansi，strict_trans_tables，traditional是一些原子模式的组合，如下：
mysql> set @@session.sql_mode='ansi';
Query OK, 0 rows affected
mysql> select @@session.sql_mode;
+-------------------------------------------------------------+
| @@session.sql_mode |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set
mysql> set @@session.sql_mode='strict_trans_tables';
Query OK, 0 rows affected
mysql> select @@session.sql_mode;
+---------------------+
| @@session.sql_mode |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set
mysql> set @@session.sql_mode='traditional';
Query OK, 0 rows affected
mysql> select @@session.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
6、mysql默认的sql mode是STRICT_TRANS_TABLES再加上两个原子模式NO_AUTO_CREATE_USER和NO_ENGINE_SUBSTITUTION，如下：
mysql> select @@global.sql_mode;
+----------------------------------------------------------------+
| @@global.sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set
mysql> show global variables like '%mode%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+----------------------------------------------------------------+
4 rows in set
| Sql注入 |
1、利用sql的语法特点，绕过正常的处理流程，入侵数据库。
2、举例来说：
void TestSqlInject(otl_connect& otlConn)
{
// 使用or
string name="Andy";
string password="1' or 1='1";
// 使用注释
//string name="Andy'/*";
//string password="*/'";
char sql[1024] = {0};
sprintf(sql,"select id from user where name='%s' and password='%s'",
name.c_str(),
password.c_str());
otl_stream stream(1, sql, otlConn);
int userId = -1;
stream>>userId;
}
这种情况，在只有用户名的情况就可以登入系统。
原因是拼接的sql语句，绕过了正常的条件检查。通过给name和password设置特殊的值，可以造成各种各样的sql注入，
在没有用户名的情况下，也可以登入系统。
3、sql注入产生的原因是：对用户的输入没有严格检查，导致拼接的sql语句表达了其他的语义。怎么解决？
4、方法1：不要拼接sql语句，而是使用绑定变量。
这种方法相当于，发给mysql一个框架，把空留着，MySQL解析，然后填空。在这种情况下，用户的输入，只是变量，
不能再表达其他的语义。如下：
void RefuseSqlInject(otl_connect& otlConn)
{
// 使用or
string name="Andy";
string password="1' or 1='1";
// 使用注释
//string name="Andy'/*";
//string password="*/'";
char sql[1024] = {0};
sprintf(sql,"select id from user "
"where name=:Name<char[100]> and password=:Password<char[100]>");
otl_stream stream(1, sql, otlConn);
int userId = -1;
stream<<name<<password;
stream>>userId;
}
5、检查用户的输入，就是检查name，password是否包含/ * or 这一类的特殊字符或者是关键字，包含直接返回错误。
可以使用mysql自带的检查方法，也可以自己实现方法，更针对性检查。
| 分割后求第n个元素 |
1、考虑下面的需求，类似Andy;Bill;Caroline，用分号分割，求出第2个元素，即Bill。
2、思路：使用substring_index，substring_index(src,delimer,n)返回一个子串，子串从src的起始，到第n个delimiter结束。
对于Andy;Bill;Caroline，使用substring_index(src,';',2)，取出Andy;Bill
reverse，为lliB;ydnA
对于lliB;ydnA，使用substring_index(src,';',1)，取出lliB
再reverse，为Bill
3、总的调用，select reverse(substring_index(reverse(substring_index('Andy;Bill;Caroline',';',2)),';',1));
- 参见
| 分割求总数 |
1、考虑下面的需求，类似Andy;Bill;Caroline，用分号分割，求出总数。
2、简单的思路，求出原字符串长度，将分号替换为空，求出二者的长度差，如下：
select length('Andy;Bill;Caroline') - length(replace('Andy;Bill;Caroline',';','')) + 1;
注：加1是因为，三个空，会有4个栏杆。
- 参见
| 删除重复记录_保存Id最小的一条 |
方法1： 1、创建一个临时表，选取需要的数据。 2、清空原表。 3、临时表数据导入到原表。 4、删除临时表。 mysql> select * from student; +----+------+ | ID | NAME | +----+------+ | 11 | aa | | 12 | aa | | 13 | bb | | 14 | bb | | 15 | bb | | 16 | cc | +----+------+ 6 rows in set mysql> create temporary table temp as select min(id),name from student group by name; Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 mysql> truncate table student; Query OK, 0 rows affected mysql> insert into student select * from temp; Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+------+ | ID | NAME | +----+------+ | 11 | aa | | 13 | bb | | 16 | cc | +----+------+ 3 rows in set mysql> drop temporary table temp; Query OK, 0 rows affected 这个方法，显然存在效率问题。 方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下： mysql> create temporary table temp as select min(id) as MINID from student group by name; Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from student where id not in (select minid from temp); Query OK, 3 rows affected mysql> select * from student; +----+------+ | ID | NAME | +----+------+ | 11 | aa | | 13 | bb | | 16 | cc | +----+------+ 3 rows in set 方法3：直接在原表上操作，容易想到的sql语句如下： mysql> delete from student where id not in (select min(id) from student group by name); 执行报错：1093 - You can't specify target table 'student' for update in FROM clause 原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。 怎么规避这个问题？ 再加一层封装，如下： mysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b); Query OK, 3 rows affected mysql> select * from student; +----+------+ | ID | NAME | +----+------+ | 11 | aa | | 13 | bb | | 16 | cc | +----+------+ 3 rows in set
| 执行sql文件，添加数据乱码 |
1、使用navicat执行sql文件，中文没有乱码。
2、直接在linux下使用mysql客户端执行，如下：
mysql -uroot -p123456 --database=niu1 <t1.sql
插入的记录显示乱码
3、怎么解决？
在sql文件的开头加上
/*!40101 SET NAMES utf8 */;
| 查看字符串的16进制取值 |
1、使用hex可以查看字符串的16进制取值，如下：
mysql> select username from user where userid=5;
+----------+
| username |
+----------+
| 吕欣盈 |
+----------+
1 row in set
mysql> select hex(username) from user where userid=5;
+---------------+
| hex(username) |
+---------------+
| C2C0D0C0D3AF |
+---------------+
1 row in set
可以看到存储格式是gb2312，因为每个中文字符占用2个字节。
2、将username字段的存储格式修改为utf8，查询16进制取值，如下：
mysql> select hex(username) from user where userid=5;
+--------------------+
| hex(username) |
+--------------------+
| E59095E6ACA3E79B88 |
+--------------------+
1 row in set
可以看到是utf8编码格式，对于utf8编码格式，一个中文字符占用3个字节。
另外：中文字符的utf8编码前4bit的取值都是E，这是因为在unicode的标准中，中文字符被框定在一个范围，前4bit都是E。
| 根据已有的记录增加新的记录 |
1、考虑下面的需求，根据已有的记录，增加新的记录，但是有些字段取值不一样。
2、示例如下：
mysql> select * from t2;
+----+----+----+---------------------+
| c1 | c2 | c3 | time |
+----+----+----+---------------------+
| 1 | 1 | 1 | 2016-04-19 20:14:45 |
+----+----+----+---------------------+
1 row in set
mysql> insert into t2(c1,c2,c3,time) select 5555,c2,c3,time from t2 where c1=1;
Query OK, 1 row affected
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+------+----+----+---------------------+
| c1 | c2 | c3 | time |
+------+----+----+---------------------+
| 1 | 1 | 1 | 2016-04-19 20:14:45 |
| 5555 | 1 | 1 | 2016-04-19 20:14:45 |
+------+----+----+---------------------+
2 rows in set
Copyright (c) 2015~2016, Andy Niu @All rights reserved. By Andy Niu Edit.