需要一些关于创建表的提示
我计划创建一个表来存储比赛结果，如下所示：
Place RaceNumber Gender Name Result
12 0112 Male Mike Lee 1:32:40
16 0117 Female Rose Mary 2:20:40
我对项目类型定义感到困惑。
我不确定
结果是否可以设置为varchar(32)或其他类型？对于
racenumber，int(11)和varchar(11)之间，哪一个更好？我可以按照我的方式使用
UNIQUE KEY吗？我需要在数据库表中将
name拆分为firstname和lastName吗？
DROP TABLE IF EXISTS `race_result`;
CREATE TABLE IF NOT EXISTS `race_result` (
`id` int(11) NOT NULL auto_increment,
`place` int(11) NOT NULL,
`racenumber` int(11) NOT NULL,
`gender` enum('male','female') NOT NULL,
`name` varchar(16) NOT NULL,
`result` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `racenumber` (`racenumber`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;
I plan to create a table to store the race result like this:
Place RaceNumber Gender Name Result
12 0112 Male Mike Lee 1:32:40
16 0117 Female Rose Mary 2:20:40
I am confused at the items type definitions.
I am not sure the
resultcan be set tovarchar(32)or other type?and for
racenumber, betweenint(11)andvarchar(11), which one is better?Can I use
UNIQUE KEYlike my way?Do I need to split
nametofirstnameandlastNamein my DB table?
DROP TABLE IF EXISTS `race_result`;
CREATE TABLE IF NOT EXISTS `race_result` (
`id` int(11) NOT NULL auto_increment,
`place` int(11) NOT NULL,
`racenumber` int(11) NOT NULL,
`gender` enum('male','female') NOT NULL,
`name` varchar(16) NOT NULL,
`result` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `racenumber` (`racenumber`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
关于数据类型的一些建议/意见。
结果 - 这是一个时间，您可能想对此时间进行一些计算，因此您应该将其存储为时间类型。
RaceNumber - 这是一个参考，虽然它是一个数字，但您不会对此数字执行任何计算。因此，您应该将其存储为 varchar 而不是 int。这将避免其用法的混乱，并避免意外地将其作为数字进行操作。
名称 - 查看名称允许的字符串长度。请小心限制该值太多。对于将来的某些名称来说，16 个字符可能太小。
地点 - 这是必需的存储空间吗？你能仅根据跑步者的成绩来计算他们的位置吗？但是，您应该为表保留一个好的主键。
Some advice/opinions regarding datatypes.
Result - This is a time, you may want to do some calculations on this time, therefore you should store it as a time type.
RaceNumber - This is a reference, whilst it is a number, you will be performing no calculations on this number. Therefore you should store it as a varchar rather than an int. This will avoid confusion as to its usage and avoid accidently manipulation of it as a number.
Name - Look at the length of string you allow for the Name. Be careful about limiting this value by so much. 16 characters may be too small for some names in the future.
Place - Is this required storage? Can you calculate the place of a runner based on their Result alone? However, you should keep a good primary key for your table.
回答您的具体问题：
结果：我只是将结果设置为整数秒。我的观点是数据应该存储在数据库中，而不是格式化。由于您可能想要对此执行的操作是按它排序并返回小于或大于它的特定值的行，因此整数对我来说似乎更好。
比赛号码：与比赛号码相同。如果它始终是数字，请使用整数并担心应用程序中的格式。如果它可以是非数字，那么无论如何将其设置为 varchar 但是，对于数字值，我看不到这样做有足够的增益。
唯一键：我真的不明白在比赛号码和 ID 上建立唯一索引有什么意义。根据定义，ID 作为主键已经是唯一的。也许你指的是比赛号码和位置，尽管如果两个人抽签争夺一个位置，即使这样也是有风险的。
拆分名称：如果您要将它们视为单独的值，那么是的。否则不行。换句话说，避免使用诸如“Mike %”之类的全名之类的内容。
In answer to your specific questions:
Result: I would just set the result to an integer number of seconds. My opinion is that data should be stored in databases, not formatting. Since the likely things you're going to want to do with this is sort by it and return rows less than or greater than specific values of it, an integer seems better to me.
Race number: Same for race number. If it's always going to be numeric, use an integer and worry about the formatting in the application. If it can be non-numeric then by all means make it varchar but, for a numeric value, I can't see enough gain in making it so.
Unique key: I don't really see the point in having a unique index on race number and ID. ID is, by definition, already unique as a primary key. Perhaps you meant race number and place although even that is risky in the event of two people drawing for a place.
Split names: If you're ever going to treat them as individual values, then yes. Otherwise no. In other words, avoid things like
where fullname like 'Mike %'.对于名称，如果您想对姓氏进行排序，同时将其显示为“名字姓氏”，那么您将需要使用单独的列。
一般来说：考虑一下您想如何处理数据。将格式留给显示数据的应用程序。避免需要字符串操作或复杂数学来获取所需值的情况。
For the name, if you ever want to sort on lastname, while you display it as "firstname lastname", then you will need to use separate columns.
In general: think about what you want to do with the data. Leave formatting to the application that is displaying the data. Avoid situations where you need string manipulation or complicated maths to get at the values you need.