TypeMoon-文章详情

Mysql杂项

最后更新时间：2022-05-22 16:21:24

Mysql

https://overtrue.me/mysql-explain/    查询 Explain

https://overtrue.me/articles/2014/10/mysql-performance-optimization-summary.html    mysql 优化

https://www.mycli.net/    mysql->Aoto

http://www.mamicode.com/info-detail-1974037.html

http://www.zsythink.net/archives/1450

------------

```
https://blog.caoyue.me/post/support-emoji-in-mysql
CREATE TABLE IF NOT EXISTS table_name (
    ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
--
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR   Variable_name LIKE 'collation%';

CREATE TABLE `test_varchar` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`city_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`realname` VARCHAR(10) NOT NULL COLLATE utf8mb4_unicode_ci DEFAULT '',
`name` VARCHAR(10) NOT NULL DEFAULT '',
`createtime` TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT test_varchar (city_id,realname,name)
VALUE 
(23, '我是谁我是谁我是谁我', '我是谁我是谁我是谁我');

CREATE DATABASE acg_one CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `ao_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `age` smallint(5) unsigned NOT NULL DEFAULT '0',
  `mobile` varchar(11) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `qq` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `sefl_content` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `id_card` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ao_user_mobile_unique` (`mobile`),
  UNIQUE KEY `ao_user_sefl_content_unique` (`sefl_content`),
  UNIQUE KEY `ao_user_id_card_unique` (`id_card`),
  KEY `ao_user_name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

ALTER TABLE topic MODIFY COLUMN content varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '内容';  
😜🤷‍
CREATE TABLE `ios_emoji` (  
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',  
  `unicode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Unicode编码',  
  `utf8` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'UTF8编码',  
  `utf16` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'UTF16编码',  
  `sbunicode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'SBUnicode编码',  
  `filename` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '文件名',  
  `filebyte` longblob COMMENT '文件内容字节',  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ios表情编码表';  
#  ROW_FORMAT=COMPACT

alter database 数据库名称 character set =utf8mb4 collate=utf8mb4_unicode_ci;
alter table 表名 convert to character set utf8mb4 collate utf8mb4_unicode_ci;

alter table 表名 CHANGE 字段名 字段名varchar(255) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

// 现在mysql8已默认支持无需修改！
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

// Mysql经纬度计算距离
SELECT *,(st_distance(point (shop_lng,shop_lat),point(108.94872 , 34.22255)) * ((6378.137 * 3.1415926535898) / 180)) as distance;

select ST_Distance_Sphere(point(108.94822, 34.22225), point(108.94872 , 34.22255)) / 1000;

SELECT *, 
ROUND(6378.137 * 2 * ASIN(SQRT(POW(SIN((34.22255 * PI() / 180 - lat * PI() / 180) / 2),2) + COS(34.22255 * PI() / 180) * COS(lat * PI() / 180) * POW(SIN((108.94872 * PI() / 180 - lng * PI() / 180) / 2),2))), 6) AS distance 
FROM table_name ORDER BY juli ASC\G
```

------------

```sql
优惠券
https://blog.csdn.net/egworkspace/article/details/80414953
CREATE TABLE `coupon` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `title` varchar(64) NOT NULL COMMENT '优惠券标题（有图片则显示图片）：无门槛50元优惠券 | 单品最高减2000元',
  `icon` varchar(128) DEFAULT NULL COMMENT '图片',
  `used` int(2) NOT NULL COMMENT '可用于：10店铺优惠券 11新人店铺券  20商品优惠券  30类目优惠券  60平台优惠券 61新人平台券',
  `type` int(2) NOT NULL DEFAULT '1' COMMENT '1满减券 2叠加满减券 3无门槛券（需要限制大小）',
  `with_special` int(2) NOT NULL DEFAULT '2' COMMENT '1可用于特价商品 2不能  默认不能(商品优惠卷除外)',
  `with_sn` varchar(36) DEFAULT NULL COMMENT '店铺或商品流水号',
  `with_amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '满多少金额',
  `used_amount` bigint(20) NOT NULL COMMENT '用券金额',
  `quota` int(10) NOT NULL DEFAULT '1' COMMENT '配额：发券数量',
  `take_count` int(10) NOT NULL DEFAULT '0' COMMENT '已领取的优惠券数量',
  `used_count` int(10) NOT NULL DEFAULT '0' COMMENT '已使用的优惠券数量',
  `start_time` datetime NOT NULL COMMENT '发放开始时间',
  `end_time` datetime NOT NULL COMMENT '发放结束时间',
  `valid_type` int(1) NOT NULL DEFAULT '2' COMMENT '时效:1绝对时效（领取后XXX-XXX时间段有效）  2相对时效（领取后N天有效）',
  `valid_start_time` datetime DEFAULT NULL COMMENT '使用开始时间',
  `valid_end_time` datetime DEFAULT NULL COMMENT '使用结束时间',
  `valid_days` int(3) NOT NULL DEFAULT '1' COMMENT '自领取之日起有效天数',
  `status` int(1) NOT NULL DEFAULT '1' COMMENT '1生效 2失效 3已结束',
  `create_user` bigint(20) NOT NULL,
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_user` bigint(20) NOT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券表';
```

```sql
# mysql查看数据库占用空间
select
TABLE_SCHEMA,
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc

# mysql查看数据表占用空间
select
TABLE_NAME,
concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables
-- where TABLE_SCHEMA LIKE 'tb_%'
order by data_size desc
limit 0,10;

```

-------

```
// mysql全文检索fulltext查询最小字段长度
// mysql 默认全文索引的最小单词是四个字符

查看索引：show index from tableName
去掉索引：alter table tableName drop index rm_bl_name_idx
添加索引：alter table tableName add FULLTEXT index rm_bl_name_idx (last_name,first_name,native_name)

// 查看设置的值
SHOW GLOBAL VARIABLES LIKE '%ft_m%

ft_min_word_len=1
innodb_ft_min_token_size=1

```

Mysql杂项

留言条数·0