MySQL数据库常用查询操作

1.查询某个表分区情况。

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,
TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='table_name';

 

2.动态创建分区函数（按时间戳每天创建1个分区）

BEGIN 
	/*每天为创建下一天的分区*/
	SET @next_date = DATE_ADD(CURDATE(),INTERVAL 1 DAY);
	SET @next_p_name = CONCAT("p", DATE_FORMAT(@next_date,"%Y%m%d"));
	SET @next_next_part_time = UNIX_TIMESTAMP(DATE_ADD(@next_date, INTERVAL 1 DAY));
	SET @add_part_sql = CONCAT("ALTER TABLE table_name ADD PARTITION (PARTITION ",@next_p_name," VALUES LESS THAN (",@next_next_part_time,"));");
	/*执行语句*/
	PREPARE stmt1 FROM @add_part_sql;
	EXECUTE stmt1;
	DEALLOCATE PREPARE stmt1;
	COMMIT;
END

 

3.插入数据，若存在相同主键的数据则更新数据

// 主键为id
INSERT INTO static_info (id, name, age) VALUES (10, 'Li', 15) on DUPLICATE key UPDATE name='Li', age=15;

 

4.删除数据，需要删除2张表中具有相同条件的数据，采用LEFT JION

DELETE umi,uaamc FROM user_marker_info umi LEFT JOIN user_area_ais_msg_cfg uaamc ON umi.id = uaamc.marker_id WHERE umi.id=2

 

5.设置数据库表字符集

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_general_ci;;