如何在Python或MySQL中校验MySQL表列值长度（如Deviceid需为6位）

你可以选择代码层校验、数据库层校验两种方案，也可以同时使用实现双重保障：

方案1：Python代码层校验（优先推荐，提前拦截避免无效数据库请求） #

在分割字符串后、执行插入SQL前新增校验逻辑，不符合要求直接抛出异常终止流程，修改后的完整代码如下：

import mysql.connector
import openpyxl

mydb = mysql.connector.connect(
  host="127.0.0.1",
  user="xxxx",
  password="xxxx",
  database="xxxx"
)

mycursor = mydb.cursor()

sp = "23|99998|211117163004|2499|5|0|4990|1|0|29259|29464|39|201116183000|39|201116183000|97830|8991000905302625027|0|1|0|0|99|1730,0630|0|0|3932196"
spl = sp.split('|')
print(spl)

# 新增Deviceid校验逻辑
device_id = spl[1]
# 校验是否为纯数字且长度恰好6位
if not (device_id.isdigit() and len(device_id) == 6):
    raise ValueError(f"非法Deviceid值：{device_id}，必须为6位纯数字")

sql = "INSERT INTO rawdata (devicetype_metermaker, Deviceid, datetime, voltage, current, powerfactor, frequency, contractorstatus, activepower, cummulative, kvah, MaximiumDemand, MaximumDemandTime, MaximumDemandkva, Date_Time, poweronhours, simnumber, tempercount, acinputstatus,  relaystatus, lightstatus, gsm, ontime, offtime, doorstatus, mcbstatus) VALUES (%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s)"
val = spl
mycursor.execute(sql, val)
mydb.commit()

# 导出Excel部分逻辑不变
wb = openpyxl.Workbook()
SQL = 'SELECT * from  rawdata '
mycursor.execute(SQL)
results = mycursor.fetchall()
ws = wb.active
ws.append(mycursor.column_names)
for row in results:
  ws.append(row)
wb.save("data100.xlsx")
mydb.commit()

方案2：MySQL数据库层校验（兜底方案，防止其他数据写入入口绕过校验） #

根据你的MySQL版本选择对应的约束添加方式：

版本要求：MySQL 8.0.16及以上（支持原生CHECK约束） #

直接执行以下SQL添加字段约束即可，后续插入非法值数据库会直接报错拒绝写入：

ALTER TABLE rawdata 
ADD CONSTRAINT chk_deviceid_6digit 
CHECK (Deviceid >= 100000 AND Deviceid <= 999999);

低版本MySQL（不支持CHECK约束） #

使用触发器实现校验，执行以下SQL创建插入前触发器：

DELIMITER //
CREATE TRIGGER trg_check_deviceid BEFORE INSERT ON rawdata
FOR EACH ROW
BEGIN
  IF NEW.Deviceid < 100000 OR NEW.Deviceid > 999999 THEN
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = 'Deviceid必须为6位纯数字';
  END IF;
END //
DELIMITER ;

注：你当前表结构中Deviceid为int类型，因此用数值范围100000~999999判断刚好对应6位数字，比转字符串判断效率更高。

内容的提问来源于stack exchange，提问作者user17453911