mysql语句

1. 获取表中全部字段

select GROUP_CONCAT(COLUMN_NAME) from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名';

2.数据表字段总数

SELECT count(1) from information_schema.COLUMNS WHERE table_schema='apollo' and table_name='special_topic_classes';

3.修改索引

ALTER TABLE statistics_classes ADD `is_deleted` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE statistics_learners ADD `is_deleted` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE statistics_subsidy ADD `is_deleted` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0';

DROP INDEX class ON `statistics_classes`; 
ALTER TABLE `statistics_classes` ADD UNIQUE class ( declareable_type, declareable_id,is_deleted );

DROP INDEX learner ON `statistics_learners`; 
ALTER TABLE `statistics_learners` ADD UNIQUE learner ( declareable_type, declareable_id,is_deleted );

DROP INDEX subsidy ON `statistics_subsidy`; 
ALTER TABLE `statistics_subsidy` ADD UNIQUE subsidy ( declareable_type, declareable_id,is_deleted );

4.导出数据

导出execl,select后添加
into outfile '/var/lib/mysql-files/XXXXXX.xls'

5.日期时间

减少 DATE_SUB(字段名, interval 30 minute)
增加 DATE_ADD(字段名, interval 30 minute)
type:SECOND,HOUR,DAY,WEEK,MONTH,YEAR
格式化 DATE_FORMAT(字段名,'%H:%i')

6.查找以逗号隔开的字符串

FIND_IN_SET() 是 MySQL 中的一个字符串函数，用于查找一个字符串在一个逗号分隔的字符串列表中的位置。如果找到该字符串，它会返回该字符串在列表中的位置（基于 1 的索引），如果没有找到，则返回 0。

INSERT INTO products (name, colors) VALUES 
('Product A', '红色,白色'), 
('Product B', '黑色,蓝色'), 
('Product C', '红色,黑色,绿色');

SELECT * FROM products WHERE FIND_IN_SET('红色', colors) > 0;

7.json数组关联

INSERT INTO products (name, img) VALUES 
('Product A', '[1,2,3]'), 
('Product B', '[4,5]'), 
('Product C', '[6]');

INSERT INTO imgs (name, url) VALUES 
('图片1', 'https://www.baidu.com/'), 
('图片2', 'https://www.baidu.com/'), 
('图片3', 'https://www.baidu.com/');

SELECT p.name, i.*
FROM products p 
JOIN imgs i ON json_contains(p.img, CAST(i.id AS json)) 
WHERE p.img!='[]' AND p.img IS NOT NULL;