MySQL的一些骚操作

最新推荐文章于 2022-11-11 11:05:09 发布
最新推荐文章于 2022-11-11 11:05:09 发布
阅读量920 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Destroyer_Dream/article/details/125387174
版权
本文详细讲解了MySQL建表语句的多种技巧，包括表结构复制、字段选择、类型转换、临时表的使用，以及分区表的设计、主键与分区规则、子查询、全文搜索、字符集设置、索引创建、用户授权和时间区间判断。涵盖了数据管理和查询优化的关键知识点。
摘要由CSDN通过智能技术生成

建表语句

1.表结构完全复制
create table user_bak LIKE user;

2.使用某些字段建表
create table user_bak select now() as time ;

3.建表时字段值强制转换
create table user_bak select CAST('2019-8-01' as UNSIGNED) as time;

4.临时表
解释: 只对当前会话有效,有同名表则原表隐藏不可见,会话结束自动清除
create temporary table user_bak  like user;
drop temporary table user_bak;

Cast类型强制转换

语法: 
    Cast(字段名 as 转换的类型 )

支持的类型:
    CHAR[(N)] 字符型 
    DATE 日期型
    DATETIME 日期和时间型
    DECIMAL float型
    SIGNED int
    TIME 时间型

场景:
    1.解决utf8字符查询时传入表情符，导致报错；

新建分区表

  • 示例
create table user_bak (
 id int(11) UNSIGNED AUTO_INCREMENT ,
 `name` varchar(200) DEFAULT null COMMENT '名称',
 rand_num int(11) DEFAULT NULL COMMENT '随机数',
 birthday datetime default null comment '生日',
 PRIMARY KEY (`id`,rand_num)
) ENGINE = INNODB partition by RANGE (rand_num)
(
	PARTITION p0 VALUES less THAN (20),
	PARTITION p1 VALUES less THAN (40),
	PARTITION p2 VALUES less THAN (60),
	PARTITION p3 VALUES less THAN (80),
	PARTITION p4 VALUES less THAN MAXVALUE
);
  • 注意点
1.PRIMARY必须包含分区的字段
2.不能单独创建分区，建表时就要创建
  • 常见异常
1.ERROR 1064  不能单独创建分区
解决：建表时就要把分区创建好

2.ERROR 1503 主键必须包含分区函数中所有列
解决：创建分区的字段必须放在主键索引中

子查询

-- 1.ALL - 查询返回单个结果,类似in操作
select * from user_bak where (id) 
>= ALL(select id from user_bak where id = 10)

-- 2.ANY & SUM -效果一样,类似or操作
select * from user_bak where (name,id) 
= SOME(select name,id from user_bak where id = 1 or name = 'eee')

FullText全文搜索

  • 全文搜索类型
1.自然语言搜索-搜索包含匹配词的信息
2.布尔模式搜索-
3.查询扩展搜索
  • 创建索引需要满足的条件
1.表类型为MyISAM,version5.6以后引入了对InnoDB支持
2.字段类型只能是char/varchar/text类型
3.全文搜索会自动忽略掉常用词(在记录中出现几率为50%以上)-验证可以查出来
4.停用词会被过滤掉(the/after/other等)
5.少于4个字符会被忽略，查不出来(默认4-84个字符范围，可更改)
  • 语法
-- 自然语言
select *,match(`name`) against('good boy') 
as 'percentage' from `user` where match(`name`) against('good boy');

-- 布尔模式
select *,match(`name`) against('good boy' in boolean MODE) as 'percentage' from `user` where match(`name`) against('good boy' in boolean MODE);

-- 内容顺序完全匹配
select *,match(`name`) against('"good boy"' in boolean MODE) as 'percentage' from `user` where match(`name`) against('"good boy"' in boolean MODE);

-- 扩展查询
select *,match(`name`) against('good boy' with query expansion) as 'percentage' from `user` where match(`name`) against('good boy'  with query expansion);
  • 修改查询字符长度
1.my.cnf文件中ft_min_word_len
2.重建FullText索引或者快速修复
repair table table_name quick;

字符集

有字符集有关的系统设置

character_set_system 用于存储的字符集
character_set_server 服务器默认字符集
collation_server  系统排序规则
character_set_database 数据库字符集
collation_database  数据库排序规则
character_set_client 客户端向服务器发送SQL时使用的字符集
character_set_result 表示服务器返回结果时使用的字符集
character_set_connection 连接时使用的字符串
character_set_filesystem 文件系统字符集

空间值

OpenGIS规范
point 类型值,只支持InnoDB/MyISAM/NDB/ARCHIVE引擎
point(xxxx,xxxx)

模糊匹配查询

1.like
    % 匹配任意数量的字符序列
    _ 只能匹配单个字符
    \%  \_  转义
2.REGEXP-正则查询

新建用户后授权

-- %表示所有IP可连接
CREATE USER `用户名`@`%` IDENTIFIED BY '密码';
grant all privileges on jwgateway.* to '用户名'@'%' identified by '密码';
select * from mysql.user;

判断时间与已有记录是否重叠

-- 1.方法一
SELECT * FROM test_table
WHERE (start_time >= startT AND start_time < endT)
   OR (start_time <= startT AND end_time > endT)
   OR (end_time >= startT AND end_time < endT)

-- 2.方法二
SELECT * FROM test_table WHERE NOT ( (end_time < startT OR (start_time > endT) )

判断时间间隔不能重叠

set @start='2022-06-08',@end='2022-06-10';
select * FROM xxx WHERE 
(
    (start_time <= @start and end_time >= @end )
     or (start_time >= @start and end_time >= @end and start_time < @end)
     or (start_time <= @start and end_time <= @end and end_time > @start )
     or (start_time >= @start and end_time <= @end)
)

-- 解释
/*
时间重叠情况
    startTime    endTime
  start |   end     |
  start |           |    end
        | start end |
        |           |
*/

MySQL8.0大数据表添加varchar字段

MySQL8.0官网文档

-- 原生只支持在表中追加字段，不可以随意位置插入字段
update xxx add column name varchar(255),ALGORITHM=INSTANT;

/*
作用：指定操作使用的算法类型
    COPY：对原始表的副本执行操作，并将表数据从原始表逐行复制到新表。不允许并发DML。
    INPLACE：操作可避免复制表数据，但可以在适当位置重建表。在操作的准备和执行阶段可以简短地获取表上的独占元数据锁定。通常，支持并发DML。
    INSTANT：操作只能修改数据字典中的元数据。在准备和执行期间，不会在表上获取任何独占元数据锁，并且表数据不受影响，从而使操作立即进行。允许并发DML。（在MySQL 8.0.12中引入）

INSTANT 原理：
    在 INNODB_COLUMNS.DEFAULT_VALUE、INNODB_COLUMNS.HAS_DEFAULT、INNODB_TABLES.INSTANT_COLS
    表中添加配置信息，标识添加instant字段前字段数、instant字段是否有默认值，instant添加的字段名;
    不会将添加的字段写入db文件，只有操作了数据(insert/update)后，才会将完整结构的数据更新至db文件
*/

查看NavicatPremium中的连接密码

1.NaivatPremium导出连接
注意：导出时一定要勾选上导出密码！！！
导出文件中 Password 值是加密后的密码
解密
1.打开网址 https://tool.lu/coderunner/  左上角选择php
2.复制以下代码到代码框中,修改倒数第三行代码中的加密串
3.点击执行(Run)
4.如果执行失败则修改下版本号 11/12
ps: 感谢大佬提供的代码，已经找不到出处了

<?php
class NavicatPassword{

	protected $version = 0;
	protected $aesKey = 'libcckeylibcckey';
	protected $aesIv = 'libcciv libcciv ';
	protected $blowString = '3DC5CA39';
	protected $blowKey = null;
	protected $blowIv = null;

	public function __construct($version = 12){
		$this->version = $version;
		$this->blowKey = sha1('3DC5CA39', true);
		$this->blowIv = hex2bin('d9c7c3c8870d64bd');
	}

	public function encrypt($string){
		$result = FALSE;
		switch ($this->version) {
			case 11:
				$result = $this->encryptEleven($string);
				break;
			case 12:
				$result = $this->encryptTwelve($string);
				break;
			default:
				break;
		}

		return $result;
	}

	protected function encryptEleven($string){
		$round = intval(floor(strlen($string) / 8));
		$leftLength = strlen($string) % 8;
		$result = '';
		$currentVector = $this->blowIv;

		for ($i = 0; $i < $round; $i++) {
			$temp = $this->encryptBlock($this->xorBytes(substr($string, 8 * $i, 8), $currentVector));
			$currentVector = $this->xorBytes($currentVector, $temp);
			$result .= $temp;
		}

		if ($leftLength) {
			$currentVector = $this->encryptBlock($currentVector);
			$result .= $this->xorBytes(substr($string, 8 * $i, $leftLength), $currentVector);
		}

		return strtoupper(bin2hex($result));
	}

	protected function encryptBlock($block){
		return openssl_encrypt($block, 'BF-ECB', $this->blowKey, OPENSSL_RAW_DATA|OPENSSL_NO_PADDING);
	}

	protected function decryptBlock($block){
		return openssl_decrypt($block, 'BF-ECB', $this->blowKey, OPENSSL_RAW_DATA|OPENSSL_NO_PADDING);
	}

	protected function xorBytes($str1, $str2){
		$result = '';
		for ($i = 0; $i < strlen($str1); $i++) {
			$result .= chr(ord($str1[$i]) ^ ord($str2[$i]));
		}

		return $result;
	}

	protected function encryptTwelve($string){
		$result = openssl_encrypt($string, 'AES-128-CBC', $this->aesKey, OPENSSL_RAW_DATA, $this->aesIv);
		return strtoupper(bin2hex($result));
	}

	public function decrypt($string){
		$result = FALSE;
		switch ($this->version) {
			case 11:
				$result = $this->decryptEleven($string);
				break;
			case 12:
				$result = $this->decryptTwelve($string);
				break;
			default:
				break;
		}

		return $result;
	}

	protected function decryptEleven($upperString){
		$string = hex2bin(strtolower($upperString));

		$round = intval(floor(strlen($string) / 8));
		$leftLength = strlen($string) % 8;
		$result = '';
		$currentVector = $this->blowIv;

		for ($i = 0; $i < $round; $i++) {
			$encryptedBlock = substr($string, 8 * $i, 8);
			$temp = $this->xorBytes($this->decryptBlock($encryptedBlock), $currentVector);
			$currentVector = $this->xorBytes($currentVector, $encryptedBlock);
			$result .= $temp;
		}

		if ($leftLength) {
			$currentVector = $this->encryptBlock($currentVector);
			$result .= $this->xorBytes(substr($string, 8 * $i, $leftLength), $currentVector);
		}

		return $result;
	}

	protected function decryptTwelve($upperString){
		$string = hex2bin(strtolower($upperString));
		return openssl_decrypt($string, 'AES-128-CBC', $this->aesKey, OPENSSL_RAW_DATA, $this->aesIv);
	}
};


//需要指定版本两种，11或12
$navicatPassword = new NavicatPassword(12);

//解密密码,替换这里的值
$decode = $navicatPassword->decrypt('xxxx');
echo $decode."\n";
?>
IT咸鱼干
关注
专栏目录
Mysql的骚操作说明
hmx224_2014的专栏
01-23 1194
GROUP_CONCAT：该函数将分组中的非NULL值串联起来称为一个字符串。如果分组中的某个值为NULL，而串联的结果为NULL。记录些不常用，但是很实用的操作，旨在在MySQL语言能解决的批量操作的问题，不动用其他动态或静态语言的辅助。CONCAT(str1，str2):返回由连接参数产生的字符串。可能有一个或多个参数。REPLACE()函数用新的子字符串替换字符串中所有出现的子字符串。注意：此函数执行区分大小写的替换。
MYSQL骚操作之第四十四话之连接与函数
HYMajor的博客
10-31 261
文章目录前言一、内连接二、左连接三、右连接![在这里插入图片描述](https://img-blog.csdnimg.cn/3cea83310e9e4340abbfb53990d6665d.png)四、数据库函数（一）数学函数（二）案例分析1、abs(X)(取绝对值)2、rand()随机数3、Mod(x,y)（取余）4、power(x,y)（次方）5、Round(X)四舍五入取整6、Round(x,y)(保留几位小数)7、Sqrt(x)8、Truncate(x,y)(保留小数点后2位，但truncate函数
关于MySQL的一些骚操作——提升正确性，抠点性能
资料免费分享，点击名片
11-16 236
概要 回顾以前写的项目，发现在规范的时候，还是可以做点骚操作的。 假使以后还有新的项目用到了MySQL，那么肯定是要实践一番的。 为了准备，创建测试数据表（建表语句中默认使用utf8mb4以及utf8mb4_unicode_ci，感兴趣的读者可以自行搜索这两个配置）： CREATE TABLE `student` ( `id` int(11) unsigned NOT NULL AUTO_IN...
【mysql】骚操作
隐市高手
04-26 1874
mysql
MySQL中的“骚操作”
qq_16268979的博客
08-17 467
1.查询指定数据库中指定的字段名存在哪些表中： SELECT * from information_schema.columns where TABLE_SCHEMA='db_name' and COLUMN_NAME='filed_name'; 2.查询很多字段但只去重其中一个或几个字段： SELECT a,b,c,d FROM TABLE WHERE id IN (SELECT min(id) FROM TABLE GROUP BY b); ...
mysql的一些 “骚操作“
MJ的博客
06-17 224
#表复制-不会复制索引 create table user2 select * from user; #倍增 insert into user2 select * from user2; #为当前会话加读写锁 读锁-自己及其它只可读，写锁-自己可读不可写，其它不可读不可写;当前会话有某表读写锁,不读写其它表；解锁只能释放当前会话的锁 LOCK TABLES vehicle write ; LOCK TABLES user2 write ; unlock TABLES ; show open t...
MYSQL骚操作之第四十三话之视图与高级逻辑语句
HYMajor的博客
10-29 163
文章目录前言一、视图1、创建视图2、展示视图3、表格化展示视图4、修改原表，显示视图二、NULL值三、MYSQL高级语句（一）MySQL 正则表达式（二）案例1、^案例2、$案例3、.案例4、|案例5、*可有可无案例6、+案例7、^[]案例8、取反四、运算符（一）算术运算符（二）比较运算符1、等于2、等于3、判断一个值为/不为null ( IS NULL、IS NOT NULL )4、BETWEEN AND5、Least and greater6、IN判断值是否在对应的列表中，是则1，否07、LIKE五、逻
MYSQL骚操作之第四十七话之SQUID代理服务器应用
HYMajor的博客
11-06 151
文章目录一、Squid代理服务器1、web 代理的工作机制2、代理的基本类型① 传统代理②透明代理③反向代理二、安装squid服务1、编译安装squid三、构建传统代理服务器1、项目环境配置2、配置传统代理四、构建透明代理服务器1、环境配置2、配置透明代理 一、Squid代理服务器 Squid主要提供缓存加速、应用层过滤控制的功能 1、web 代理的工作机制 ①代替客户机向网站请求数据，从而可以隐藏用户的真实IP地址 ②将获得的网页数据（静态 web 元素）保存到缓存中并发送给客户机，以便下次请求相同的数据
Marks:一些日常想法以及骚操作记录
04-08
一些日常想法记录 都是日常几分钟-1h的小脚本 0*: nlp 1*: python 2*: scrawl 3*: pyspark 4*：aho-corasick 5*: ft源码相关 6*: mysql相关 7*: es相关 9*: 工具类
mysql骚操作_Mysql骚操作：优化大分页查询
weixin_39753791的博客
02-19 90
背景系统结构如上图。经过排查是因为系统B拉取数据时间太长导致的推送超时。系统B拉取数据的方法是根据_tiemstamp(数据操作时间)分页查询系统A的接口,即：1SELECT字段名2FROM表名3WHERE_timestamp>=beginTimeAND_timestamp<=endTime4LIMITn,m;由于该数据是从其他数据源中导入的，所以_timest...
mysql骚操作_《关于MySQL的一些骚操作》
weixin_42299566的博客
01-27 153
概要回顾以前写的项目，发现在规范的时候，还是可以做点骚操作的。假使以后还有新的项目用到了MySQL，那么肯定是要实践一番的。为了准备，创建测试数据表(建表语句中默认使用utf8mb4以及utf8mb4_unicode_ci，感兴趣的读者可以自行搜索这两个配置)：CREATE TABLE `student` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT...
MySQL相关骚操作
微笑的丁总的个人博客
08-13 190
Windows找不到mysql服务了： 命令行进入安装目录的Bin下. 输入mysqld.exe –install, 回车. 进入服务界面中选中MySQL选项，右键属性将“启动类型”设置为”自动”，”服务状态”设置为“已启动”。 windows无法启动mysql 1053： 第一步： mysqld-nt remove mysqlservice 的方式卸载...
mysql 骚（常规）操作
weixin_34191734的博客
01-07 132
2019独角兽企业重金招聘Python工程师标准>>> ...
巧用这19条MySQL优化，效率至少提高3倍
weixin_34216196的博客
01-02 1726
本文我们来谈谈项目中常用的MySQL优化方法，共19条，具体如下： 1、EXPLAIN 做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例，标注（1、2、3、4、5）我们要重点关注的数据： type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列，使用到的索引名。如果没有选择索引，...
mysql骚操作
weixin_30236595的博客
01-15 130
授予用户最大的权限，所有的权限 grant all privileges on *.* to username@'%' identified by 'password'; 移除权限 revoke all privileges on *.* from 用户@"%" identified by '密码'; 数据库的备份与恢复 备份 mysqldump -u root -p --all-database...
盘点编写 sql 上的那些骚操作（针对mysql而言）
最新发布
风团团
11-11 705
咋说呢，最近交接了一个XXX统计系统到我手上，点进去系统主页，看了下实现了哪些功能，页面看着很简单就那么几个统计模块，本来想着就那么几张报表的crud来着，看了下代码也还好体量也不大，于是乎美滋滋的随波逐流了，后来出现了一个bug说什么数据统计的不对，想着快速给他改掉，顺着控制层，一路摸索到mapper这，点进xml文件一看，好家伙我尼玛一个统计sql 400多行，故事的正片由此开始！
mysql骚操作_关于MySQL的一些骚操作——提升正确性，抠点性能
weixin_31600305的博客
01-27 145
概要回顾以前写的项目，发现在规范的时候，还是可以做点骚操作的。假使以后还有新的项目用到了MySQL，那么肯定是要实践一番的。为了准备，创建测试数据表(建表语句中默认使用utf8mb4以及utf8mb4_unicode_ci，感兴趣的读者可以自行搜索这两个配置)：CREATE TABLE `student` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT...
mysql 查询排名 返回值拼接的骚操作
ancaofumuhu9385的博客
01-07 126
故事背景： 依照原系统的框子搞一个新的系统给其他人使用，因为新的系统配置库依然需要使用原有的表，表中有字段区分新的系统，然后就有了这个很没劲的数据同步。 难点：配置库码表数据的主键之前是自增，后来改造改成18位有意义的字符序列，但是由于部分小哥哥小姐姐不按照规范写，导致表中主键有部分数据是前几位一样，有的是后几位一样。 数据同步方案一： 使用截取原有主键一部分，再在其前面拼...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值