标签 mysql 下的文章

mysql全文索引实现模糊查询

配置mysql的ngram,打开mysql.ini的配置文件,编辑在[mysqld]下面加入这样的配置

# vim /etc/my.cnf
[mysqld]
ngram_token_size=2
ft_min_word_len=1 //最小分词长度(默认是4,小于4则忽略搜索)

保存退出,并重启mysql

# service mysql restart

再登入mysql,并通过命令查看:

mysql> show variables like 'ngram_token_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 1     |
+------------------+-------+

创建测试表

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `data` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `name` (`name`) WITH PARSER ngram 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test VALUES(null,'welcome to you!',unix_timestamp());
INSERT INTO test VALUES(null,'this is the fan site of you xp 你好',unix_timestamp());
INSERT INTO test VALUES(null,'hello phpjs,you are welcome',unix_timestamp());
INSERT INTO test VALUES(null,'fuck you tomorrow!!',unix_timestamp());

阅读

mysql 数据库关联多张表

表一:

CREATE TABLE `log_zz` (
  `dt` datetime NOT NULL,
  `info` varchar(100) NOT NULL,
  KEY `dt` (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

表二:

CREATE TABLE `log_xx` (
  `dt` datetime NOT NULL,
  `info` varchar(100) NOT NULL,
  KEY `dt` (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

表三:

CREATE TABLE `log_yy` (
  `dt` datetime NOT NULL,
  `info` varchar(100) NOT NULL,
  KEY `dt` (`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

关联表:

CREATE TABLE `log_merge` (
  `dt` datetime NOT NULL,
  `info` varchar(100) NOT NULL,
  KEY `dt` (`dt`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 UNION=(`log_zz`,`log_xx`,`log_yy`);

这是三张表的数据:
D5Q7}IL{10TRQGAF@47D~61.png

这是关联表的数据:

关联表数据

执行代码

查询:select * from log_merge;
删除:delete from log_merge where `dt`='2020-08-10 10:48:53';
更新:update table where  `dt`='2020-08-10 10:48:53' set `info` = 'SS';

MySQL权限安全总结- 用户权限管理及数据库备份(mysqldump)与恢复

1.安全管理

一、管理用户
mysql的用户账号信息存储在名为mysql的数据库中。
获得所有用户账号列表:

mysql> use mysql;
Database changed
mysql> select user from user;
+-------+
| user  |
+-------+
| root  |
|       |
| cacti |
| root  |
|       |
| root  |
+-------+
6 rows in set (0.00 sec)

mysql>

二、创建用户账号
使用CREATE USER语句:

mysql> create user rokas identified by 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> select user from user;
+-------+
| user  |
+-------+
| rokas |
| root  |
|       |
| cacti |
| root  |
|       |
| root  |
+-------+
7 rows in set (0.00 sec)

mysql>

identified by指定用户密码,省略indentified by语句则为空密码
identified by指定的用户密码为纯文本,在保存到user表之前会对其加密处理。 identified by password 'xxx'则为指定加密过后的密码

三、重命名用户账号(RENAME)
示例:

mysql> rename user rokas to anthony;
Query OK, 0 rows affected (0.00 sec)

mysql>

这仅对于mysql5之后的版本有效。也可以用UPDATE来更新user表,这样对版本无要求。

四、删除用户账号(DROP USER)
删除用户账号(以及权限),使用DROP USER语句:

mysql> drop user anthony;
Query OK, 0 rows affected (0.00 sec)

mysql>

五、设置及撤销访问权限(GRANTREVOKE)
在创建用户账号后,必须接着分配访问权限。不然它们只能登录MySQL,不能看到数据,不能执行任何数据库操作。
为看到赋予用户账号的权限,使用SHOW GRANTS FOR语句:

mysql> create user rokas identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for rokas;
+------------------------------------------------------------------------------------------------------+
| Grants for rokas@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rokas'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>flush privileges;      ###刷新权限

输出结果显示用户rokas有一个权限USAGE ON *.*USAGE表示没有任何权限,所以,此结果表示rokas在任意数据库及表上没有任何权限。
用户定义为user@host
MySQL的权限用用户名和主机名结合定义。如果不指定主机名,则使用默认的主机名%(授予用户访问权限而不管主机名)

示例,创建用户只允许本地登录:

create user 'rokas'@'localhost' identified by 'password';

为设置权限,使用GRANT语句。要求给出以下信息:

mysql> grant select on crashcourse.* to rokas;
Query OK, 0 rows affected (0.01 sec)

mysql>flush privileges;

GRANT允许用户在crashcourse.*(crashcourse数据库所有表)上使用SELECT
通过只授予SELECT访问权限,用户rokascrashcourse数据库中的所有数据具有只读访问权限。

显示这个更改:

mysql> show grants for rokas;
+------------------------------------------------------------------------------------------------------+
| Grants for rokas@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rokas'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT ON `crashcourse`.* TO 'rokas'@'%'                                                       |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

GRANT的反操作为REVOKE,用来撤销特定权限,示例:

mysql> revoke select on crashcourse.* from rokas;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

这条语句撤销用户rokascrashcourse数据库的SELECT访问权限。

GRANTREVOKE可在以下几个层次上控制访问权限:

权限说明
ALLGRANT OPTION外的所有权限
ALTER使用ALTER TABLE
ALTER ROUTINE使用ALTER PROCEDUREDROP PROCEDURE
CREATE使用CREATE TABLE
CREATE ROUTINE使用CREATE PROCEDURE
CREATE TEMPORARY使用CREATE TEMPORARY TABLE
CREATE USER使用CREATE USERDROP USERRENAME USERREVOKE ALL PRIVILEGES
CREATE VIEW使用本身
DELETE使用本身
DROP使用DROP TABLE
EXECUTE使用CALL和存储过程
FILE使用SELECT INTO OUTFILELOAD DATA INFILE
GRANT OPTION使用GRANTREVOKE
INDEX使用CREATE INDEXDROP INDEX
INSERT使用本身
LOCK TABLES使用本身
PROCESS使用SHOW FULL PROCESSLIST
RELOAD使用FLUSH
REPLICATION CLIENT服务器位置的访问
REPLICATION SLAVE由复制从属使用
SELECT使用本身
SHOW DATABASES使用本身
SHOW VIEW使用SHOW CREATE VIEW
SHUTDOWN使用mysqladmin shutdown(用于关闭MySQL)
SUPER使用CHANGE MASTERKILLLOGSPURGEMASTERSET GLOBAL。还允许mysqladmin调试登录
UPDATE使用本身
USAGE无访问权限

根据上表配合GRANTREVOKE,可以完全控制用户权限。

如果有多个权限,可以用逗号分隔:

GRANT SELECT,INSERT ON crashcourse.* to rokas;

注意:在使用GRANTREVOKE时,用户账号必须存在,而涉及的对象没有要求。这允许管理员在创建数据库和表之前设计和实现安全控制。

六、更改密码(SET PASSWORD)
更改用户密码,使用SET PASSWORD语句。
示例:

mysql> set password for rokas=password('new password');
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

set password更新用户密码,新密码必须传递到Password函数进行加密。

设置当前用户的口令:

mysql> set password=password('123456');
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

2.数据库备份及维护

一、数据库备份及还原(mysqldump)
1)备份

格式:

mysqldump -h主机名 -P端口 -u用户名 -p密码(后不跟密码则会交互输入密码) --database 数据库名 >文件名.sql

示例:

mysqldump -hlocalhost -P3306 -uroot -p --database www >www.bak.sql
mysqldump --all-databases > allbackupfile.sql
mysqldump -hhostname -uusername -ppassword -database databasename | gzip > backupfile.sql.gz

2)还原
格式:mysql -h主机名 -u用户名 -p密码 <文件名.sql

示例:

mysql -hlocalhost -uroot -p123456 <www.bak.sql

或者使用source:

mysql>use mysql;
mysql>source test.sql;
zcat bakupfile.sql.gz | mysql -uuser -ppassword

二、确保数据库正确及正常运行,使用以下语句来检查健康状态

1)ANALYZE TABLE,用来检查表键是否正确

示例:

mysql> analyze table orders;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| course.orders | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.00 sec)

mysql>

2)CHECK TABLE用来针对许多问题对表进行检查。
示例:

mysql> check table orders,orderitems;
+-------------------+-------+----------+----------+
| Table             | Op    | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| course.orders     | check | status   | OK       |
| course.orderitems | check | status   | OK       |
+-------------------+-------+----------+----------+
2 rows in set (0.00 sec)

mysql>

如果MyISAM引擎的表产生不正确和不一致的结果,可能需要用REPAIR TABLE来修复相应的表。但不应该经常使用,可能会造成更大的问题要解决。

3)OPTIMIZE TABLE
如果从一个表中删除大量数据,应该使用OPTIMIZE TABLE来收回所用空间,优化表的性能。
示例:

mysql>optimize table orders;

三、诊断启动问题
在排除系统启动问题时,首先应该尽量手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动,下面是几个重要的mysqld命令行选项:
--help 显示帮助
--safe-mode装载减去某些最佳配置的服务器
--verbose显示全文本消息(为获得更详细的帮助消息与--help联合使用)
--version显示版本信息

四、查看日志文件
MySQL主要日志文件有以下几种:

  1. 错误日志。包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err(可用--log-error命令行选项更改),位于data目录中。
  2. 查询日志。记录所有mysql活动,在诊断问题时非常有用。此日志通常名为hostname.log(可用--log命令行选项更改),位于data目录。
  3. 二进制日志。记录更新过数据(或者可能更新过数据)的所有语句,名为hostname-bin(可用--log-bin命令行选项更改),位于data目录。
  4. 缓慢查询日志。此日志记录执行缓慢的任何查询,名为hostname-slow.log(用--log-slow-queries选项更改)

在使用日志时,可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

mysql如何提高处理查询速度

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0 

3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=10 or num=20 

可以这样查询:

select id from t where num=10 
union all 
select id from t where num=20

5.in 和 not in 也要慎用,否则会导致全表扫描,如:

select id from t where num in(1,2,3) 

对于连续的数值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3 

6.下面的查询也将导致全表扫描:

select id from t where name like '%abc%' 

若要提高效率,可以考虑全文检索。

7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

select id from t where num=@num 

可以改为强制查询使用索引:

select id from t with(index(索引名)) where num=@num 

8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num/2=100 

应改为:

select id from t where num=100*2 

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3)='abc'--name以abc开头的id 
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30'生成的id

应改为:

select id from t where name like 'abc%' 
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' 

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询,如需要生成一个空表结构:

select col1,col2 into #t from t where 1=0 

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

create table #t(...) 

13.很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b) 

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num) 

14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select from t ,用具体的字段列表代替“”,不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作,提高系统并发能力。

30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

laravel闭包用法

DB::table('vods')->where('id', "=", $value->id)->where(function ($query) use ($keyword) {
    $query->where('dd_str', '<', mb_strlen($data))->orWhere('dd_str', '=', null);
    })->update([
        'dd' => $data,
        'dd_str' => mb_strlen($data)
]);    
备案号:粤ICP备18155514号-2