- DROP TABLE IF EXISTS t_buckets ;
- CREATE TEMPORARY TABLE t_buckets
- (
- id INT AUTO_INCREMENT PRIMARY KEY,
- buckets_content VARCHAR(500)
- );
- -- split by "]," and get single bucket content
- WHILE (INSTR(v_histogram,'],')>0) DO
- INSERT INTO t_buckets(buckets_content)
- SELECT SUBSTRING(v_histogram,1,INSTR(v_histogram,'],'));
- SET v_HISTOGRAM = SUBSTRING(v_histogram,INSTR(v_histogram,'],')+2,LENGTH(v_histogram));
- END WHILE;
- INSERT INTO t_buckets(buckets_content)
- SELECT v_histogram;
- -- get the basic statistics data
- WITH cte AS
- (
- SELECT
- HISTOGRAM->>'$."last-updated"' AS last_updated,
- HISTOGRAM->>'$."number-of-buckets-specified"' AS number_of_buckets_specified
- FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
- WHERE schema_name = p_schema_name
- AND table_name = p_table_name
- AND column_name = p_column_name
- )
- SELECT
- CASE WHEN id = 1 THEN p_schema_name ELSE '' END AS schema_name,
- CASE WHEN id = 1 THEN p_table_name ELSE '' END AS table_name,
- CASE WHEN id = 1 THEN p_column_name ELSE '' END AS column_name,
- CASE WHEN id = 1 THEN last_updated ELSE '' END AS last_updated,
- CASE WHEN id = 1 THEN number_of_buckets_specified ELSE ''END AS'number_of_buckets_specified' ,
- id AS buckets_specified_index,
- buckets_content
- FROM
- (
- SELECT * FROM cte,t_buckets
- )t;
来源: http://www.linuxidc.com/Linux/2018-08/153703.htm