如何防止在使用 mysql 的 VARCHAR 列中使用数字? [英] How to prevent using digits in a VARCHAR column using mysql?

问题描述

我有一个名为 user 的表，它将通过 user_id 和 username 进行查询.user_id 属于 int 类型，username(属于 varchar 类型)不应only 包含数字.该程序可以帮助检查输入，但我想通过不允许数据库级别上已经存在的纯数字来双重保护它.

I have a table called user, and it would be queried both by user_id and username. whilst user_id is of type int, username (being of type varchar) should not only contain digits. The program could help checking the input, but I want to double secure it by not allowing purely numbers already on the level of the database.

如何做到这一点?

推荐答案

您正在寻找 CHECK SQL 运算符.不幸的是，任何 MySQL 引擎都不支持这个.

You are looking for the CHECK SQL operator. Unfortunately, this one is not supported by any of the MySQL engines.

不过，有一种解决方法，即使用 SQL 触发器.您可以在 MySQL 中的检查约束不起作用和http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

There is a workaround, though, by making use of SQL triggers. You may find more information at CHECK constraint in MySQL is not working and http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

因此，对我而言(需要 MySQL 5.5+)，以下代码段有效:

So, for me (MySQL 5.5+ required), the following snipplet worked:

use test;

CREATE TABLE user (
  `user_id` INT NOT NULL,
  `username` VARCHAR(50) NULL,
  PRIMARY KEY (`id`)
);

delimiter //

create trigger trg_trigger_test_ins before insert on user
for each row
begin
    if new.username REGEXP '^[0-9]*$' then
        signal sqlstate '45000' set message_text = 'Invalid username containing only digits';
    end if;
end
//

delimiter ;

insert into user values(1, '1234');

最后一个 INSERT 语句引发了 错误代码:1644.无效的用户名仅包含数字 while

The last INSERT statement raised an Error Code: 1644. Invalid username containing only digits whilst

insert into `test`.`user` values(1, 'a1234');

已正确插入.

注意:一些荣誉也抛出错误signal 想法上的 MySQL 触发器.

NB: Some kudos also to Throw an error in a MySQL trigger on the signal idea.

请注意，此示例仅针对 INSERT 提供保护.如果你想安全，你还需要在UPDATE上实现一个触发器.

Note that this example only protects against INSERTs. If you want to be safe, you also need to implement a trigger on UPDATE.

这篇关于如何防止在使用 mysql 的 VARCHAR 列中使用数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！