MySQL利用procedure analyse()函数优化表结构
简介
procedureanalyse()函数是MySQL内置的对MySQL字段值进行统计分析后给出建议的字段类型。
语法
procesureanalyse(max_elements,max_memory)
max_elements
指定每列非重复值的最大值，当超过这个值的时候，MySQL不会推荐enum类型。
max_memory
analyse()为每列找出所有非重复值所采用的最大内存大小。
实战演练
#对t1表所有的列进行分析 wing@3306>showcreatetablet1; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ |Table|CreateTable| +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ |t1|CREATETABLE`t1`( `id`int(11)DEFAULTNULL, `name`varchar(16)DEFAULTNULL, `score`int(11)DEFAULTNULL )ENGINE=InnoDBDEFAULTCHARSET=utf8| +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ 1rowinset(0.00sec) wing@3306>select*fromt1procedureanalyse(4); +---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+ |Field_name|Min_value|Max_value|Min_length|Max_length|Empties_or_zeros|Nulls|Avg_value_or_avg_length|Std|Optimal_fieldtype| +---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+ |wing.t1.id|1|200000|1|6|0|0|100000.5000|116099.2790|MEDIUMINT(6)UNSIGNEDNOTNULL| |wing.t1.name|000jxc6V|zzznmkcX|8|8|0|0|8.0000|NULL|CHAR(8)NOTNULL| |wing.t1.score|1|100|1|3|0|0|50.4889|28.8768|TINYINT(3)UNSIGNEDNOTNULL| +---------------+-----------+-----------+------------+------------+------------------+-------+-------------------------+-------------+--------------------------------+ 3rowsinset(0.14sec)
总结
以上就是关于MySQL中procedureanalyse()函数的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。