MySQL 数据类型?
我正在为基本 CMS 设计 MySQL 和 PHP 数据库。 CMS 将有一个允许排序和搜索的前端。后端将允许授权用户上传文件。
我正在使用 PHPMyAdmin,我需要帮助设置我的数据库。
我愿意接受解释各种 MySQL 数据类型及其用途的答案。请使用常见的数据库字段作为示例。
下面是我想要的列表。缺少什么以及我需要使用什么数据类型?
资源(对于我的文件)
- 文件 ID
- 文件名(文件已预先排序并派生显示名称和路径 从这里开始。)
- file_type (PDF | AUDIO | VIDEO | PHOTO [也用于生成文件 网址])
- 上传日期(PHP 或 MySQL 中的时间戳)
- uploaded_by(用户表中的用户 ID)
- 事件(事件表中的 event_id,可选)
用户(用户帐户 - 用于管理员访问,也可能是通知列表)
- 用户 ID
- 名字
- 姓氏
- 电子邮件
- 密码
- 电话号码(可选)
- permissions_level(只读、上传)
- 创建日期
活动
- event_id
- 事件名称
- 活动地点
- 事件日期
- 事件描述
- entry_date
I'm designing a database in MySQL and PHP for a basic CMS. The CMS will have a front end which will allow sorting and searching. The backend will allow authorized users to upload files.
I'm using PHPMyAdmin and I'd like help setting up my database.
I am open to answers explaining various MySQL datatypes and what they are good for as well . Use common database fields as examples please.
Below is a list of what I'd like. What's missing and what datatypes do I need to use?
Resources (For my files)
- file_id
- filename (Files are presorted and display names and paths are derived
from here.)- file_type (PDF | AUDIO | VIDEO | PHOTO [Also used to generate file
urls])- upload date (timestamp in PHP or MySQL)
- uploaded_by (User ID from Users table)
- event (event_id from Events table, optional)
Users (User accounts - for admin access and maybe a notification list)
- user_id
- first_name
- last_name
- password
- phone_number (optional)
- permissions_level (read only, upload)
- creation_date
Events
- event_id
- event_name
- event_location
- event_date
- event_description
- entry_date
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我会采取什么:
资源(对于我的文件)
用户(用户帐户 - 用于管理员访问,可能还有通知列表)
事件< /strong>
当您设置数据库并输入一些虚拟数据时,您可以通过 phpmyadmin 运行一个简单的语句,它会告诉您 MySQL 会为该确切的虚拟数据采取什么:
在列 < code>Optimal_fieldtype 你会发现MySQL告诉你采取什么。但您不应该采用确切的字段类型。它会经常告诉您采用 ENUM,但大多数时候您添加随机数据,因此您必须采用 VARCHAR,在这种情况下,列
Max_length
会提示您应该有多长。但在所有 VARCHAR 字段上,您应该根据您期望值的长度添加额外的空间。请注意,即使是名称也可能超过 50 个字符。What I would take:
Resources (For my files)
Users (User accounts - for admin access and maybe a notification list)
Events
When you have set up your database and input some dummy data, you can run a simple statement through phpmyadmin that will tell you, what MySQL would take for that exact dummy data:
In the column
Optimal_fieldtype
you will find what MySQL tells you to take. But you should not take that exact fieldtype. It will tell you very often to take a ENUM but most of the time you add random data so you have to take a VARCHAR in that cases the columnMax_length
will give you a hint on how long it should be. But on all VARCHAR fields you should add additonal space depending on how long you expect the values to be. Take in consideration that even a name can be longer than 50 chars.您的用户表没有密码哈希列。不确定你是否打算让它有这样的。我不明白我们如何回答应该使用什么数据类型,因为它完全取决于您计划如何使用列。对于日期,我更喜欢日期时间而不是时间戳,但这只是个人偏好,因为我喜欢在查询中手动插入日期。
Your user table doesnt have a column for the password hash. Not sure if you intended for it to have such or not. I cant see how we can answer what datatypes should be used, since it completely depends on how you plan on using the columns. For dates, I prefer datetimes over timestamps, but thats just a personal preference as I like to manually insert the dates in the queries.
缺少什么,那么这需要您来决定/需要什么。您应该阅读设计数据库。
至于您所拥有的数据类型
id字段应该是
INT
或BIGINT
(取决于您的应用程序可能变得有多大)并设置为主键
。名称应该是
varchar
您想要的长度取决于您的要求。大多数名字/列表名称通常最多 25-30 个字符。事件名称最多可达 250 个,具体取决于您的要求。该位置将类似于
VARCHAR
的名称,大约在 50-150 之间,具体取决于您的要求。日期应该是
DATETIME
字段。描述应该是
VARCHAR(250)
或TEXT
字段。权限实际上取决于您想要如何处理它。这可以是
INT
或VARCHAR
(如果您想序列化数组)。电话号码可以是
INT
(如果您想删除所有非数字字符并以您自己的方式格式化),也可以是VARCHAR(15)
(如果您愿意)不想删除字符EMail 应为
VARCHAR(250)
。希望这会有所帮助,这实际上取决于您对应用程序的要求以及您的设想。但初始类型总是可以随着您的需求的变化而改变。
编辑
如果您想了解有关不同 MySQL 数据类型的完整信息,请阅读手册:http://dev.mysql.com/doc/refman/5.0/en/data-types.html
What is missing, well that is sort of for you to decide / what is required. You should read up on Designing databases.
As far as the datatypes for what you have
The id fields should be a
INT
, orBIGINT
(depends on how big your application may become) and set as thePRIMARY KEY
.The names should be a
varchar
how long you want it depends on what your requirements are. Most first / list names are generally 25-30 characters max. Event names could be upwards to 250, depending on your requirements.The location will be similar to the name as a
VARCHAR
somewhere around 50-150, depending on your requirements.The date should be a
DATETIME
field.The description should be either a
VARCHAR(250)
or aTEXT
field.The permissions really depends on how you want to handle it. This could be an
INT
or aVARCHAR
(incase you want to serialize an array).Phone number could be an
INT
(if you want to strip all non-numeric characters and format it your own way) or aVARCHAR(15)
if you do not want to strip the charactersEMail should be a
VARCHAR(250)
.Hopefully this helps, again it really depends on your requirements for the application and what you have envisioned. But the initial types can always be changed as your requirements change.
EDIT
And if you want to know full information about the different MySQL Data Types, read the manual: http://dev.mysql.com/doc/refman/5.0/en/data-types.html