Как исправить Out of range value в MySQL

 1-- Проверь определение колонки
 2DESCRIBE table_name;
 3SHOW COLUMNS FROM table_name;
 4
 5-- Проверь максимальные значения
 6SELECT column_name, data_type, 
 7       CASE data_type
 8           WHEN 'tinyint' THEN '[-128, 127] или [0, 255]'
 9           WHEN 'smallint' THEN '[-32768, 32767] или [0, 65535]'
10           WHEN 'int' THEN '[-2147483648, 2147483647] или [0, 4294967295]'
11           WHEN 'bigint' THEN '[-9223372036854775808, 9223372036854775807]'
12           ELSE 'Проверь документацию'
13       END as range
14FROM information_schema.columns 
15WHERE table_name = 'table_name';

 1-- Измени TINYINT на INT
 2ALTER TABLE table_name 
 3MODIFY COLUMN column_name INT;
 4
 5-- Измени INT на BIGINT
 6ALTER TABLE table_name 
 7MODIFY COLUMN column_name BIGINT;
 8
 9-- Измени на UNSIGNED для больших положительных чисел
10ALTER TABLE table_name 
11MODIFY COLUMN column_name INT UNSIGNED;

 1-- Проверь текущее значение автоинкремента
 2SELECT AUTO_INCREMENT 
 3FROM information_schema.TABLES 
 4WHERE TABLE_SCHEMA = 'database_name' 
 5AND TABLE_NAME = 'table_name';
 6
 7-- Исправь значение автоинкремента
 8ALTER TABLE table_name AUTO_INCREMENT = 1;
 9
10-- Проверь максимальное значение в колонке
11SELECT MAX(id) FROM table_name;

 1# Python валидация диапазона
 2def validate_int_range(value, min_val, max_val):
 3    if not isinstance(value, int):
 4        raise ValueError(f"Value must be integer, got {type(value)}")
 5    if value < min_val or value > max_val:
 6        raise ValueError(f"Value {value} out of range [{min_val}, {max_val}]")
 7    return value
 8
 9def validate_unsigned_int(value, max_val):
10    if not isinstance(value, int):
11        raise ValueError(f"Value must be integer, got {type(value)}")
12    if value < 0 or value > max_val:
13        raise ValueError(f"Value {value} out of range [0, {max_val}]")
14    return value
15
16def insert_with_validation(data):
17    try:
18        # Валидация перед вставкой
19        validated_user_id = validate_int_range(data['user_id'], 1, 2147483647)
20        validated_age = validate_unsigned_int(data['age'], 255)
21        
22        conn = mysql.connector.connect(
23            host='localhost', user='user', password='password', database='db'
24        )
25        cursor = conn.cursor()
26        
27        sql = "INSERT INTO users (user_id, age) VALUES (%s, %s)"
28        cursor.execute(sql, (validated_user_id, validated_age))
29        conn.commit()
30        
31        return {'success': True, 'id': cursor.lastrowid}
32        
33    except ValueError as e:
34        return {'success': False, 'error': str(e)}
35    except Exception as e:
36        return {'success': False, 'error': str(e)}
37    finally:
38        cursor.close()
39        conn.close()

 1# Обработка переполнения автоинкремента
 2def insert_with_auto_increment_handling(data):
 3    try:
 4        conn = mysql.connector.connect(
 5            host='localhost', user='user', password='password', database='db'
 6        )
 7        cursor = conn.cursor()
 8        
 9        # Проверь текущее значение автоинкремента
10        cursor.execute("""
11            SELECT AUTO_INCREMENT 
12            FROM information_schema.TABLES 
13            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
14        """, ('database', 'table_name'))
15        
16        auto_increment = cursor.fetchone()[0]
17        
18        # Если автоинкремент близок к максимуму
19        if auto_increment > 2147483640:  # Для INT
20            # Измени тип на BIGINT
21            cursor.execute("ALTER TABLE table_name MODIFY COLUMN id BIGINT AUTO_INCREMENT")
22            conn.commit()
23        
24        # Вставь данные
25        sql = "INSERT INTO table_name (name, email) VALUES (%s, %s)"
26        cursor.execute(sql, (data['name'], data['email']))
27        conn.commit()
28        
29        return {'success': True, 'id': cursor.lastrowid}
30        
31    except Exception as e:
32        return {'success': False, 'error': str(e)}
33    finally:
34        cursor.close()
35        conn.close()

 1# range_monitor.py
 2import mysql.connector
 3from datetime import datetime
 4
 5def check_column_ranges_mysql(host, user, password, database, table):
 6    try:
 7        conn = mysql.connector.connect(
 8            host=host, user=user, password=password, database=database
 9        )
10        cursor = conn.cursor()
11        
12        # Получи информацию о числовых колонках
13        cursor.execute("""
14            SELECT column_name, data_type, numeric_precision, numeric_scale
15            FROM information_schema.columns 
16            WHERE table_name = %s AND table_schema = %s
17            AND data_type IN ('tinyint', 'smallint', 'int', 'bigint', 'decimal')
18        """, (table, database))
19        
20        columns = cursor.fetchall()
21        results = {}
22        
23        for column in columns:
24            col_name, data_type, precision, scale = column
25            
26            # Проверь диапазон значений в колонке
27            cursor.execute(f"""
28                SELECT MIN({col_name}) as min_val, 
29                       MAX({col_name}) as max_val,
30                       COUNT(*) as total_rows
31                FROM {table}
32                WHERE {col_name} IS NOT NULL
33            """)
34            
35            result = cursor.fetchone()
36            if result:
37                min_val, max_val, total_rows = result
38                
39                # Определи допустимый диапазон
40                if data_type == 'tinyint':
41                    max_range = 127 if 'unsigned' not in str(column).lower() else 255
42                    min_range = 0 if 'unsigned' in str(column).lower() else -128
43                elif data_type == 'int':
44                    max_range = 2147483647 if 'unsigned' not in str(column).lower() else 4294967295
45                    min_range = 0 if 'unsigned' in str(column).lower() else -2147483648
46                elif data_type == 'bigint':
47                    max_range = 9223372036854775807
48                    min_range = -9223372036854775808
49                else:
50                    max_range = min_range = None
51                
52                results[col_name] = {
53                    'data_type': data_type,
54                    'min_value': min_val,
55                    'max_value': max_val,
56                    'total_rows': total_rows,
57                    'min_range': min_range,
58                    'max_range': max_range,
59                    'near_limit': max_val > max_range * 0.9 if max_range else False
60                }
61        
62        cursor.close()
63        conn.close()
64        
65        return {
66            'table': table,
67            'columns': results,
68            'timestamp': datetime.now()
69        }
70    except Exception as e:
71        return {
72            'table': table,
73            'error': str(e),
74            'timestamp': datetime.now()
75        }
76
77# Мониторинг
78status = check_column_ranges_mysql(
79    'localhost', 'user', 'password', 'database', 'users'
80)
81print(f"Range check: {status}")

 1# auto_fix_range.py
 2def fix_column_range_mysql(host, user, password, database, table, column, new_type):
 3    try:
 4        conn = mysql.connector.connect(
 5            host=host, user=user, password=password, database=database
 6        )
 7        cursor = conn.cursor()
 8        
 9        # Получи текущее определение колонки
10        cursor.execute(f"""
11            SELECT data_type, is_nullable, column_default, extra
12            FROM information_schema.columns 
13            WHERE table_name = %s AND column_name = %s AND table_schema = %s
14        """, (table, column, database))
15        
16        col_info = cursor.fetchone()
17        if col_info:
18            data_type, is_nullable, column_default, extra = col_info
19            
20            # Создай новое определение колонки
21            nullable = "NULL" if is_nullable == "YES" else "NOT NULL"
22            default = f"DEFAULT {column_default}" if column_default else ""
23            auto_increment = "AUTO_INCREMENT" if "auto_increment" in extra.lower() else ""
24            
25            sql = f"ALTER TABLE {table} MODIFY COLUMN {column} {new_type} {nullable} {default} {auto_increment}"
26            cursor.execute(sql)
27            
28            conn.commit()
29            cursor.close()
30            conn.close()
31            
32            return {
33                'success': True,
34                'message': f"Column {column} changed to {new_type}"
35            }
36        else:
37            return {
38                'success': False,
39                'error': f"Column {column} not found"
40            }
41    except Exception as e:
42        return {
43            'success': False,
44            'error': str(e)
45        }