字符串作为主键？
我有这个数据库结构
CREATE TABLE `productinfo` (
`ProductID` int(11) NOT NULL AUTO_INCREMENT,
`ProductName` varchar(255) NOT NULL,
`ProductImage` varchar(255) NOT NULL,
`CategoryID` int(11) NOT NULL,
`SubCategoryID` int(11) NOT NULL,
`ProductBrief` varchar(255) NOT NULL,
`Features` text NOT NULL,
`Specifications` text NOT NULL,
`Reviews` text NOT NULL,
`Price` varchar(255) NOT NULL,
`Status` tinyint(4) NOT NULL,
PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
，现在我需要将 ProductID、CategoryID 和 SubCategoryID 转换为像 Ps-5678 这样的字符串作为零件号。 ProductID 是主键，那么我如何更改数据库的结构。 CategoryID 和 SubCategoryID 是其他表中的主键，所以我该如何处理这个..是否像变成
`ProductID` int(11) NOT NULL AUTO_INCREMENT
字符串一样简单..并摆脱
PRIMARY KEY (`ProductID`)
想法，建议任何人
I have this database structure
CREATE TABLE `productinfo` (
`ProductID` int(11) NOT NULL AUTO_INCREMENT,
`ProductName` varchar(255) NOT NULL,
`ProductImage` varchar(255) NOT NULL,
`CategoryID` int(11) NOT NULL,
`SubCategoryID` int(11) NOT NULL,
`ProductBrief` varchar(255) NOT NULL,
`Features` text NOT NULL,
`Specifications` text NOT NULL,
`Reviews` text NOT NULL,
`Price` varchar(255) NOT NULL,
`Status` tinyint(4) NOT NULL,
PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
I now I need to turn ProductID, CategoryID, and SubCategoryID into a string like Ps-5678 for the part number. ProductID is the primary key so how do i change the structure of the database. CategoryID, and SubCategoryID are primary keys in other tables so how do i handle this..is it as easy as turning
`ProductID` int(11) NOT NULL AUTO_INCREMENT
into a string..and getting rid of
PRIMARY KEY (`ProductID`)
ideas, suggestions anyone
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
主键用于数据库。
显示名称供最终用户使用。
不要将一个与另一个混淆！不要用有意义的东西来创建主键。你迟早会后悔的。
将代理键/身份/自动编号作为主键是一个非常好的主意，并且在数据库设计中广泛使用。
您可以添加列甚至派生列并在其上添加唯一约束。
Primary keys are for the database.
Display names are for end users.
Do not confuse one with another! Don't make a primary key out of something that has a meaning. You will regret it sooner or later.
Having a surrogate key / identity / autonumber as a primary key is a very good idea and is used widely in database design.
You can add a column or even a DERIVED COLUMN and add a unique constraint on it.
我相信这应该是那么容易。
但是您需要确定要使用的字符串类型
http://dev.mysql.com/doc/refman/5.1 /en/string-types.html
I believe it should be that easy.
however you need to determine what string type you want to use
http://dev.mysql.com/doc/refman/5.1/en/string-types.html
你的要求不清楚。如何从 3 int 列中获取“PS-5678”？您的示例中只有 2 个组件。
您是否只需要将 3 个 INT 转换为单个 CHAR() 字符串？
如果是这样，数据库就没问题，根本不需要改表！？！？！这三个组件已经可用，正确分离，作为不同的列。您所寻找的只是将三个组件显示为单个字符串。
Your requirement is unclear. How do you get "PS-5678" fro 3 int columns ? There is only 2 components in your example.
Do you just need to CONVERT the 3 INTs to a single CHAR() string ?
If so, the database is fine, no need to change the table at all !?!?! The three components are already available, correctly seperated, as distinct columns. What you are looking for is merely DISPLAYING the three components as a single string.
从你的问题中我不清楚你对产品、类别和子类别做了什么来制作你的零件号。为了便于论证，我假设您将它们连接在一起，例如产品 123、类别 456、子类别 789 给出部件号 123-456-789 等。
只要可行，我喜欢使用自然标识符作为主键。但“只要可行”可能是一个严重的限制。如果您的自然标识符是通过以某种方式组合其他三个字段而派生的，则您有四种选择：
使主键成为这三个字段的组合。这往往是一种痛苦。然后，所有连接都必须在三个字段上匹配，搜索必须测试三个字段，等等。
创建一个由三个字段串联而成的新字段，并将其用作主键。然后，每当“基本”字段之一发生更改时，也会更改此串联字段。这是一个非常非常糟糕的主意。不要这样做。这是冗余数据，所有不好的东西都来自冗余数据。
将三个单独的字段替换为一个组合字段。这比#2 更糟糕。现在，当您需要单独的值时，您必须将该字段分开。
放弃并创建一个合成密钥，例如序列号。使用它作为主键，然后仅使用自然键进行显示。如果我的自然密钥需要连接或以其他方式操作三个字段，我倾向于选择此选项。
It's not clear to me from your question just what you do to product, category, and subcategory to make your part number. I'll assume for the sake of argument that you are concatenating them together, like product 123, category 456, subcategory 789 gives part number 123-456-789 or some such.
I like to use natural identifiers as primary keys whenever practical. But "whenever practical" can be a serious constraint. If your natural identifier is derived by somehow combining three other fields, you have four choices:
Make the primary key be the combination of these three fields. This tends to be a pain. All your joins then must match on three fields, searches must test three fields, etc.
Create a new field that is the concatenation of the three fields, and use this as the priamry key. Then whenever one of the "basic" fields changes, also change this concatenated field. This is a very, very bad idea. Don't do it. It's redundant data, with all the bad things that come from redundant data.
Replace the three separate fields with one combined field. This is worse than #2. Now when you need the individual values, you have to take the field apart.
Give up and create a synthetic key, like a serial number. Use this as the primary key, and then just use the natural key for display purposes. If my natural key requires concatenating or otherwise manipulating three fields, I tend to go with this option.