mysql 日常使用

1. 给某个字段增加随机48~200 并限制条数

UPDATE cou_data.qpl_xxxxx SET default_saves_num = FLOOR(RAND() * (200 - 48 + 1) + 48)
where id > 0 and default_saves_num = 0 limit 10000;

2. 将一张表的的某一个字段更新到另一张表中

UPDATE cou_data.qp_xxx t2 INNER JOIN cou_data.qp_xxx_inxxx t1 ON t2.id = t1.mapping_term_id
SET t1.mapping_term_name = t2.name, t1.mapping_term_urlname = t2.urlname where t2.id < 1000;

3, 将sql字段的url 更新到 domain 字段

SELECT 
    SUBSTRING_INDEX(url, '://', -1) AS path,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 3), '://', -1) AS host,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url, '?', 1), '#', 1) AS base_url,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url, '?', 1), '/', -1) AS resource,
    SUBSTRING_INDEX(url, '#', 1) AS url_no_fragment,
    SUBSTRING_INDEX(url, '?', 1) AS url_no_query_string,
    SUBSTRING_INDEX(url, '=', -1) AS anchor
FROM 
    (SELECT 'http://www.example.com/path/to/page?param=value#anchor' AS url) AS url_table;
 
### 实践实现
UPDATE table_name SET domain = replace(SUBSTRING_INDEX(SUBSTRING_INDEX(merchant_website, '/', 3), '://', -1),'www.','') where id > 0;
 

4，mysql 替换更新

### 将xxx替换成bbb
update xxx.xxx_coupon set title = replace(title,'xxxx ', 'bbbb')  where id = 0;

5，mysql查询title里面的数字大于1000

SELECT * FROM xxxx.xxx_coupon
WHERE title REGEXP '[[:digit:]]+' and title > '1000';

6，mysql命令行导出数据库

mysqldump -u username -p your_database > your_database_dump.sql

7，mysql命令行导出单表

mysqldump -u 用户名 -p 数据库名 表名 > 表名.sql
mysqldump -u username -p dbname table_name >file.sql
 
mysqldump -u username -p your_database table1 table2 > your_database_tables_dump.sql

8，mysql 生成一个自增的序列号

select row_number() over (order by id) as order_number ,id  from XXX_tag where tag_status =1;

9，mysql 使用json字段

 select
        replace(json_extract(faq_tpl, '$[0].title'),'"','') title1,replace(json_extract(faq_tpl, '$[0].answer'),'"','') answer1,
        replace(json_extract(faq_tpl, '$[1].title'),'"','') title2,replace(json_extract(faq_tpl, '$[1].answer'),'"','') answer2,
        replace(json_extract(faq_tpl, '$[2].title'),'"','') title3,replace(json_extract(faq_tpl, '$[2].answer'),'"','') answer3,
        replace(json_extract(faq_tpl, '$[3].title'),'"','') title4,replace(json_extract(faq_tpl, '$[3].answer'),'"','') answer4,
        replace(json_extract(faq_tpl, '$[4].title'),'"','') title5,replace(json_extract(faq_tpl, '$[4].answer'),'"','') answer5
 
 from xxxx_data order by id asc;

9，字段字符集冲突

### 设置表字符集
alter table hhhh_term_site default character set utf8mb4 collate=utf8mb4_general_ci;
alter table hhhh_term_faq_site default character set utf8mb4 collate=utf8mb4_general_ci;
 
### 查看字符集
SHOW FULL COLUMNS FROM hhhh_term_site;
SHOW FULL COLUMNS FROM hhhh_term_faq_site;
### 设置字符集
ALTER TABLE `hhhh_term_site` CHANGE `domain` `domain` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `hhhh_term_faq_site` CHANGE `domain` `domain` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

10 mysql 取一张表两条sql 分别限制30 10

要在MySQL中同时执行两个查询，每个查询分别限制结果的数量为30和10，可以使用子查询。以下是一个示例代码：

SELECT * FROM (
  SELECT * FROM your_table LIMIT 30
) AS subquery1
 
UNION ALL
 
SELECT * FROM (
  SELECT * FROM your_table LIMIT 10
) AS subquery2;

11 mysql 查询最后一个反斜杠，并且把反斜杠替换成空

SELECT  substr(site_url,2) AS last_char
 FROM `hot`.i5_rewrite_xxxxx WHERE RIGHT(site_url, 1) = '\/';
 
 
SELECT count(1)
 FROM `hot`.i5_rewrite_xxxxx WHERE RIGHT(site_url, 1) = '\/';
 
SELECT count(1)
 FROM `hot`.i5_rewrite_xxxxx WHERE is_sync = 88;
 
 
update  i5_rewrite_xxxxx set is_sync = 88 WHERE RIGHT(site_url, 1) = '\/';
 
update i5_rewrite_xxxxx set site_url = SUBSTRING(site_url, 1,  length(site_url) - 1)   WHERE is_sync = 88;
 
select * from hot.i5_rewrite_xxxxx where is_sync = 88;
 
SELECT
 
    SUBSTRING(site_url, 1,  length(site_url) - 1),
    SUBSTRING(site_url,  length(site_url)  + 1)
 
 FROM `hot`.i5_rewrite_xxxxx WHERE RIGHT(site_url, 1) = '\/';

查询json里面的字段

 select
         ifnull(replace(json_extract(json_content, '$.data.page_type'),'"',''),'') page_type,
         ifnull(replace(json_extract(json_content, '$.data.hasAff'),'"',''),'no') has_aff,
         ifnull(LOWER(replace(json_extract(json_content, '$.data.term.CountryCode'),'"','')),'') country,
         ifnull(replace(json_extract(json_content, '$.data.term.Name'),'"',''),'') term_name,
         ifnull(replace(json_extract(json_content, '$.data.term.DomainUrl'),'"',''),'') domain,
         ifnull(replace(json_extract(json_content, '$.data.term.Image'),'"',''),'') source_img,
        mapping_id id
 from i5_rewrite_url_mapping_json order by id asc;