mysql 统计 字段_MySQL统计字段

最近开发需要检查生产库中每个表的字段设置是否合理,比如tinyint是否快达到上线峰值了.于是做了一个Python脚本,这里贴出来分享一下

(转载请注明作者出处)

[mysql@localhost ~]$ cat check_field.py

#!/usr/bin/env python

#coding:utf8

from __future__ import division

import os

import sys

import time

from commands import getstatusoutput as getso

user = 'xxxxxx'           ---用户

password= 'xxxxxx'     ---密码

aaa = '''请正确填写参数！

例如: python check_field.py -h hostname -p port'''

if len(sys.argv) != 5:

print sys.argv

print aaa

sys.exit(1)

print 1

elif sys.argv[1] != '-h' or sys.argv[3] != '-p':

print aaa

sys.exit(1)

else:

pass

def TTT(single, total):

remove = single/total

return remove

os.system('''clear ''')

print "\033[1;32;40m库名               表名                      字段                         类型                最大值           范围\033[0m"

Cmd = '''/usr/local/mysql/bin/mysql -h %s -u%s -p%s -P %s -e 'show databases' ''' % (sys.argv[2],user,password,sys.argv[4])

status, result_database = getso(Cmd)

if status != 0:

print result_database

else:

for i in result_database.split('\n'):

if i == 'Database' or i == 'information_schema' or i == 'mysql' or i == 'performance_schema':

pass

else:

Cmd = '''/usr/local/mysql/bin/mysql -h %s -u%s -p%s -P %s -e 'use %s; show tables' ''' % (sys.argv[2],user,password,sys.argv[4],i)

status, result_table = getso(Cmd)

for Biao in result_table.split('\n')[1:]:

Cmd = '''/usr/local/mysql/bin/mysql -h %s -u%s -p%s -P %s -e 'use %s; desc %s' ''' % (sys.argv[2],user,password,sys.argv[4],i,Biao)

status, result_field = getso(Cmd)

for Field in result_field.split('\n')[1:]:

arg = Field.split()

if 'int' in arg[1]:

Cmd = '''/usr/local/mysql/bin/mysql -h %s -u%s -p%s -P %s -e 'use %s; select max(`%s`) from %s' ''' % (sys.argv[2],user,password,sys.argv[4],i,arg[0],Biao)

status, result_value = getso(Cmd)

if 'tinyint' in arg[1]:

try:

if 'unsigned' == arg[2]:

Back = TTT(int(result_value.split('\n')[1]), 255)

else:

Back = TTT(int(result_value.split('\n')[1]), 127)

#print Back

except ValueError:

Back = 20

rang = '有符号：-127 ~ 127 无符号：0 ~ 255'

elif 'smallint' in arg[1]:

try:

if 'unsigned' == arg[2]:

Back = TTT(int(result_value.split('\n')[1]), 65535)

else:

Back = TTT(int(result_value.split('\n')[1]), 32767)

except ValueError:

Back = 20

rang = '有符号：-32768 ~ 32767 无符号：0 ~ 65535 '

elif 'mediumint' in arg[1]:

try:

if 'unsigned' == arg[2]:

Back = TTT(int(result_value.split('\n')[1]), 16777215)

else:

Back = TTT(int(result_value.split('\n')[1]), 8388607)

except ValueError:

Back = 20

rang = '有符号：-8388608 ~ 8388607 无符号：0 ~ 16777215 '

elif 'bigint' in arg[1]:

try:

if 'unsigned' == arg[2]:

Back = TTT(int(result_value.split('\n')[1]), 18446744073709551615)

else:

Back = TTT(int(result_value.split('\n')[1]), 9223372036854775807)

except ValueError:

Back = 20

rang = '有符号：-9223372036854775808 ~ 9223372036854775807 无符号：0 ~ 18446744073709551615'

elif 'integer' in arg[1] or 'int' in arg[1]:

try:

if 'unsigned' == arg[2]:

Back = TTT(int(result_value.split('\n')[1]), 4294967295)

else:

Back = TTT(int(result_value.split('\n')[1]), 2147483647)

except ValueError:

Back = 20

rang = '有符号：-2147483648 ~ 2147483647 无符号：0 ~ 4294967295'

else:

if 'unsigned' == arg[2]:

Back = TTT(int(result_value.split('\n')[1]), 255)

else:

Back = TTT(int(result_value.split('\n')[1]), 127)

rang = ''

if 'unsigned' == arg[2]:

Type_C = arg[1]+' '+arg[2]

else:

Type_C = arg[1]

DataBase_Len = len(i)

Table_Len = len(Biao)

Field_Len = len(arg[0])

Type_Len = len(arg[1])

Value_Len = len(result_value.split('\n')[1])

#                        if Back < 0.0001 or Back > 80:

Form = i+' '*(19-DataBase_Len)+Biao+' '+' '*(39-Table_Len)+arg[0]+' '+' '*(29-Field_Len)+Type_C+' '+' '*(19-Type_Len)+result_value.split('\n')[1]+' '*(19-Value_Len)+rang

print Form

#print i,Biao,arg[0], arg[1],result_value.split('\n')[1]

#time.sleep(5)

执行方式:

(1)需要在MySQL中建立相应的用户

(2)[mysql@localhost ~]$python check_field.py -h localhost -p 3306

结果样式如下:

库名               表名                      字段                         类型                最大值           范围

P-test             test                      uid         mediumint(8) unsigned        2057275        有符号：-8388608 ~ 8388607 无符号：0 ~ 16777215

阅读(5806) | 评论(0) | 转发(0) |