如何在 mysql (laravel) 中使 varchar 可以为空且唯一 [英] how to make a varchar nullable and unique together in mysql (laravel)

问题描述

我可以将一个 MySQL 列设为可空且唯一吗?如果用户想提供，我有一个存储用户 Email_id 的表，否则它将是(空).我在其他一些问题中读到，我可以使用默认的 NULL 创建一个唯一的字段.但是我在创建表时出现这个错误

Can i make a MySQL column nullable and unique together. I have a table that store users Email_id if user wants to provide else it will be (null). I read in some other questions that i can make a unique field with default NULL. but i get this error when creating table

#1067 - Invalid default value for 'email' (i make it only for test purpose)

主表是用larave schema builder类生成的

the main table is generated with larave schema builder class

$table->text('email')->nullable()->unique(); (no default added)

在数据库中

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+ 
| email_id     | varchar(200)     | YES  | UNI | NULL    |                |

现在错误是

Duplicate entry '' for key 'users_email_id_unique' (when inserting email with empty string) 

现在的问题是如何处理一个唯一且可以为空的 varchar 字段.

Now question is how to handle a varchar field which is unique and nullable together.

推荐答案

我知道这是一个老问题，这是给有同样问题的其他人的.

I know this is an old question, this is for others who have the same issue.

您正在尝试添加一个空字符串.您使列可为空和唯一.这意味着您正在尝试不应该做的事情.您可以发送多个 null 值，但不能发送多个 空 字符串.MySQL 将 empty 视为一个值.

You are trying to add an empty string. You made column nullable and unique. That means you are trying something you shouldn't. You can send multiple null values but not mutiple empty string. MySQL consider empty as a value.

或者你可以在你的模态中写一个 mutator

or you could write a mutator in your modal

public function setEmailAttribute($value) {
    if ( empty($value) ) { // will check for empty string
    $this->attributes['email'] = NULL;
    } else {
        $this->attributes['email'] = $value;
    }
}

这篇关于如何在 mysql (laravel) 中使 varchar 可以为空且唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！