为什么 MySQL 不允许在 VARCHAR 列上使用 auto_increment? [英] Why doesn't MySQL allow auto_increment on VARCHAR columns?

问题描述

我有以下 SQL 语句:

I have the following SQL statement:

CREATE TABLE patient(
   patient_id VARCHAR(10) NOT NULL DEFAULT 1000 AUTO_INCREMENT PRIMARY KEY);

这个声明不起作用，我不明白为什么.我到处都看到 AUTO_INCREMENT 只适用于一个整数.为什么它只适用于整数?如果我想要混合使用 INT 和 CHAR 怎么办，为什么默认值不起作用?

This statement does not work, and I do not understand why. Everywhere I see AUTO_INCREMENT only works with an integer. Why is it only for integers? What if I want a mix of INT and CHAR, and why doesn't the default value work?

推荐答案

此查询不起作用

错误消息告诉您原因.简单地说，你不能auto_increment 字符数据.你真的希望它做什么?

The error message is telling you why. Simply put, you can't auto_increment character data. What would you even expect that to do, really?

即使它被实现了(我想它可能是，但 MySQL 团队从来没有一个令人信服的理由这样做)，你会用不可打印和不可打字的字符做什么在你的主键中?想象一个以 BELL 字符作为主键的记录.这听起来不好玩.

Even if it was implemented (which I imagine it could be, but the MySQL team never had a compelling reason to do so), what would you do with un-printable and un-typable characters in your primary key? Imagine a record with the BELL character as the primary key. That doesn't sound like fun.

无论我在哪里看到 auto_increment 仅适用于 int

wherever I see auto_increment works with only int

因为只有整数才能有意义地递增.它们对每个增量都有一个非常明确的步骤和一个非常明确的值边界.其他数据类型没有.

Because only integers can meaningfully be incremented. They have a very well defined step for each increment and a very well defined boundary of values. Other data types don't.

如果我希望它是 char 和 int 混合怎么办

what if I want it to be char and int mix up

想要某样东西并不能让它发生.关系数据库中的每一列都必须有一个定义的类型.一列不能有多种类型.

Wanting something doesn't make it happen. Each column in a relational database has to have a defined type. One column can't have more than one type.

为什么默认值不起作用

因为您将列定义为字符数据，但将默认值定义为数值.字符数据的默认值必须是字符值.

Because you defined the column as character data, but defined the default as a numeric value. The default for character data has to be a character value.

这篇关于为什么 MySQL 不允许在 VARCHAR 列上使用 auto_increment?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！