带前缀的自动增量ID

本文介绍了带前缀的自动增量ID的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在设计一个表'employees'，其中包含一个主键，该主键是自动递增的，代表员工的ID。

I'm designing a table 'employees', which contains an primary key which is auto increment and represents an ID of the employee.

我想给ID带有指定城市的数字：城市1：1，城市2：2，等等。

I want to prefix the ID with an number designating the city: city 1: 1, city 2:2, etc.

因此，这些ID应该看起来像xyy，其中x代表城市，而yy员工的ID。

So the IDs should look like xyy where x represents the city and yy the ID of the employee.

当我添加新员工时，我要选择城市x，并且我希望yy值自动递增。

When I'm adding new employee I'm selecting the city x, and I would like to yy values to auto increment.

是否可以使用SQL命令？

Is that possible using SQL commands?

推荐答案

您实际上将两个字段打包为一个并违反了原子性和1NF的原理。最重要的是，您的密钥不是最小的（可以这么说）。

You are effectively packing two fields into one and violating the principle of atomicity and the 1NF in the process. On top of that, your key is not minimal (so to speak).

相反，请保留两个单独的字段：ID和CITY。

Instead, keep two separate fields: ID and CITY.

ID 单独是主键。用您自己的话说，ID是自动递增的，因此它是唯一的。

ID alone is the primary key. In your own words, ID is auto-increment, so it alone is unique.

您可以轻松地并置 ID和CITY一起在查询或VIEW中甚至在客户端代码中显示。没有理由在表本身中预煮连接的值。

You can easily concatenate ID and CITY together for display purposes in your query or VIEW or even in the client code. There is no reason to "precook" the concatenated value in the table itself.