blocks|key|1010180|text|在创建一个专门供将来使用的表时，您必须问的主要问题是如何检索这些数据以及它有什么用途。就我个人而言，我总是有一个惟一的标识符，通常是表的ID。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1010181|查看您的列表似乎没有任何唯一定义条目的内容，因此您将无法跟踪重复的条目，也无法唯一地检索记录。|1010182|如果您想保持这种设计，可以创建一个由name和param-value组成的复合主键。|1010183|CREATE+TABLE+testtest+(
++ID++++INT++NOT+NULL++PRIMARY+KEY++AUTO_INCREMENT,
++Name++VARCHAR(100)++NOT+NULL,
++value+number++NOT+NULL
/*add+other+fields+here*/
);

CREATE+TABLE+testtest+(
++name++VARCHAR(100)++NOT+NULL,
++value+int++NOT+NULL,
/*add+other+fields+here*/
++primary+key(name,value)
);|code-block|syntax|javascript|1010184|这些create+table示例表达了上面提到的两个选项。|1010185|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

The main question you have to ask when creating a table specially for future use is how will this data be retrieved and what purpose it is having. Personally I always have a unique identifier usually an ID to the table.

Looking at you list do not seem to have anything that uniquely defines the entries so you will not be able to track duplicate entries nor uniquely retrieve a record.

If you want to keep this design you could create a composite primary key composed of the name and the param-value.

<pre><code>CREATE TABLE testtest (
 ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 Name VARCHAR(100) NOT NULL,
 value number NOT NULL
/*add other fields here*/
);

CREATE TABLE testtest (
 name VARCHAR(100) NOT NULL,
 value int NOT NULL,
/*add other fields here*/
 primary key(name,value)
);
</code></pre>

Those create table example express the 2 above mentioned options.

blocks|key|5454740|text|根据我的经验，这种按行存储字段的想法需要非常仔细地考虑-尽管它似乎提供了许多优点，但它使许多常见的任务变得更加困难。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5454741|积极的一面是:它很容易扩展，而不需要更改数据库的结构，并且在某些方面抽象了数据存储的细节。|5454742|消极的一面:你需要查看所有按列存储的字段在DBMS中自动提供给你的所有日常事务:简单的内部/外部连接，一条语句插入/更新，唯一性，外键和其他数据库级约束检查，简单的搜索结果过滤和排序。|5454743|考虑一下您的体系结构中的一个查询，该查询返回y和z之间具有MetalLayer=X和宽度的所有项-按长度排序的结果。与使用列存储特定字段相比，该查询对您来说更难构造，对DBMS来说也更难执行。|5454744|总而言之，我唯一一次使用您建议的结构是在需要临时添加随机的非结构化附加数据的上下文中。在我看来，如果我不能使更传统的表结构工作，这将是最后的策略。|5454745|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

In my experience this idea of storing fields row-wise needs to be considered extremely carefully - although it seems give many advantages it makes many common tasks much more difficult.

On the positive side: It is easily extensible without changes to the structure of the database and in some ways abstracts the details of the data storage.

On the negative side: You need to look at all the everyday things storing fields column-wise gives you automatically in the DBMS: Simple inner/outer joins, one statement inserts/updates, uniqueness, foreign keys and other db level constraint checking, simple filtering ad ordering of search results.

Consider in your architecture a query to return all items with MetalLayer=X and Width between y and z - results sorted by Coupling by length. This query is much harder for you to construct and much, much harder for the DBMS to execute than it would be using columns to store specific fields.

In the balance the only time I have used a structure like the one you suggest was in a context where random unstructured additional data needed to be added on an ad-hoc basis. In my opinion this would be a last resort strategy if there was no way I could make a more traditional table structure work.

blocks|key|3556975|text|您建议的内容称为|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3556976|它有几个缺点，比如在执行引用完整性约束方面存在严重困难。此外，您必须提出的查询将比您的第二个建议使用规格化表(具有列的表：Primary+Key,+Width,+Length,+Space,+Coupling,+etc)要复杂一些。|offset|length|style|CODE|3556977|因此，对于一个简单的项目，不要使用EAV模型。|3556978|如果您的计划是针对更复杂的项目，并且您希望获得最大的灵活性，那么也不要使用EAV。您应该研究一下，它在MySQL中更难实现，当然也不是一项容易的任务。但如果你成功了，你将拥有这两样东西:灵活性和最高级别的规范化(有些人称"EAV“为"6NF做错了”)。|3556979|entityMap^0|0|1P|1C|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

What you suggest is called <a href="http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model" rel="nofollow"><code>EAV model (Entity–Attribute–Value)</code></a>

It has several drawbacks like severe difficulties in enforcing referential integrity constraints. In addition, the queries you'll have to come up with, will be a bit more complicated than with a normalized table as your second suggestion (table with columns: <code>Primary Key, Width, Length, Space, Coupling, etc</code>).

So, for a simple project, do not use EAV model.

If your plans are for a more complex project and you want maximum flexibility, do not use EAV either. You should look into <a href="http://en.wikipedia.org/wiki/Sixth_normal_form" rel="nofollow"><code>6NF (6th Normal Form)</code></a> which is even harder to implement and certainly not an easy task in MySQL. But if you succeed, you'll have both goods: flexibility and normalization to the highest level (some people call "EAV" as "6NF done wrongly").

blocks|key|5170687|text|不，对于关系数据库，这是一个糟糕的设计。这是Entity-Attribute-Value设计的一个例子。它很灵活，但它打破了关系数据库的大部分规则。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|5170688|在深入EAV设计作为灵活数据库的解决方案之前，请阅读这个故事：Bad+CaRMa。|5170689|更具体地说，EAV的一些问题包括：|5170690|在不查询任何给定constraints.|5170691|You的情况下，您无法知道任何给定VARCHAR.|unordered-list-item|5170692|
|5170693|Particularly存在哪些属性。|5170694|5170695|您无法将任何属性设置为强制属性，等同于NOT+NULL。|5170696|5170697|您不能使用数据库value不能使用MySQL数据类型；VARCHAR列必须是MySQL中的long+VARCHAR，每个VARCHAR都存储在其自己的数据页上，因此这是非常浪费的。|style|CODE|5170698|5170699|当您使用EAV设计时，查询也非常复杂。Magento，一个开源的电子商务平台，广泛使用EAV，许多用户说，如果你需要自定义报告，它非常慢，很难查询。|5170700|要成为关系型属性，您应该将每个不同的属性存储在其自己的列中，并使用其自己的名称和适当的数据类型。|5170701|我已经在我的presentation+Practical+Object-Oriented+Models+in+SQL和我的博客文章EAV+FAIL以及我的书SQL+Antipatterns:+Avoiding+the+Pitfalls+of+Database+Programming中写了更多关于EAV的内容。|5170702|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Entity%25E2%2580%2593attribute%25E2%2580%2593value_model|1|http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/|2|http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql|3|http://karwin.blogspot.com/2009/05/eav-fail.html|4|http://www.pragprog.com/titles/bksqla/sql-antipatterns^0|M|M|0|0|V|9|1|0|0|0|0|0|0|0|0|0|8|5|0|0|0|0|J|13|2|1T|8|3|26|1R|4|0^^$0|@$1|2|3|4|5|6|7|1L|8|@]|9|@$A|1M|B|1N|1|1O]]|C|$]]|$1|D|3|E|5|6|7|1P|8|@]|9|@$A|1Q|B|1R|1|1S]]|C|$]]|$1|F|3|G|5|6|7|1T|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|1U|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|1V|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|1W|8|@]|9|@]|C|$]]|$1|O|3|P|5|L|7|1X|8|@]|9|@]|C|$]]|$1|Q|3|N|5|6|7|1Y|8|@]|9|@]|C|$]]|$1|R|3|S|5|L|7|1Z|8|@]|9|@]|C|$]]|$1|T|3|N|5|6|7|20|8|@]|9|@]|C|$]]|$1|U|3|V|5|L|7|21|8|@$A|22|B|23|W|X]]|9|@]|C|$]]|$1|Y|3|-4|5|6|7|24|8|@]|9|@]|C|$]]|$1|Z|3|10|5|6|7|25|8|@]|9|@]|C|$]]|$1|11|3|12|5|6|7|26|8|@]|9|@]|C|$]]|$1|13|3|14|5|6|7|27|8|@]|9|@$A|28|B|29|1|2A]|$A|2B|B|2C|1|2D]|$A|2E|B|2F|1|2G]]|C|$]]|$1|15|3|-4|5|6|7|2H|8|@]|9|@]|C|$]]]|16|$17|$5|18|19|1A|C|$1B|1C]]|1D|$5|18|19|1A|C|$1B|1E]]|1F|$5|18|19|1A|C|$1B|1G]]|1H|$5|18|19|1A|C|$1B|1I]]|1J|$5|18|19|1A|C|$1B|1K]]]]

No, this is a bad design for a relational database. This is an example of the <a href="http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model" rel="noreferrer">Entity-Attribute-Value</a> design. It's flexible, but it breaks most rules of what it means to be a relational database.

Before you descend into the EAV design as a solution for a flexible database, read this story: <a href="http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/" rel="noreferrer">Bad CaRMa</a>. 

More specifically, some of the problems with EAV include:

<ul>
<li>You don't know what attributes exist for any given ID_NUM without querying for them.</li>
<li>You can't make any attribute mandatory, the equivalent of NOT NULL.</li>
<li>You can't use database constraints.</li>
<li>You can't use SQL data types; the <code>value</code> column must be a long VARCHAR.</li>
<li>Particularly in MySQL, each VARCHAR is stored on its own data page, so this is very wasteful.</li>
</ul>

Queries are also incredibly complex when you use the EAV design. Magento, an open-source ecommerce platform, uses EAV extensively, and many users say it's very slow and hard to query if you need custom reports.

To be relational, you should store each different attribute in its own column, with its own name and an appropriate datatype.

I have written more about EAV in my presentation <a href="http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql" rel="noreferrer">Practical Object-Oriented Models in SQL</a> and in my blog post <a href="http://karwin.blogspot.com/2009/05/eav-fail.html" rel="noreferrer">EAV FAIL</a>, and in my book, <a href="http://www.pragprog.com/titles/bksqla/sql-antipatterns" rel="noreferrer">SQL Antipatterns: Avoiding the Pitfalls of Database Programming</a>.

blocks|key|1007545|text|这里有几件事需要考虑：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1007546|不存在单个主键。这可以通过使主键由两列组成来克服(如Carl+T的第二个示例)|1007547|Param列是重复的，为了使其规范化，您应该查看MGA的示例。|1007548|第三，"Metal+layer“列是一个字符串，而不是像其他列那样的浮点值。|1007549|所以最好的方法是像下面这样定义一个表：|1007550|create+table+yourTable(
++ID+int+primary+key,
++ParamId+int+not+null,
++Width+float,
++Length+float,
++Space+float,
++Coupling+float,
++Metal_layer+varchar(20),
++Foreign+key(ParamID)+references+Param(ID),
++Value+varchar(20)
)
create+table+Param(
++ID+int+primary+key,
++Name+varchar(20)
)|code-block|syntax|javascript|1007551|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|V|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

A few things to consider here:

There is no single primary key. This can be overcome by making the primary key consist of two columns (like in the second example of Carl T)

The Param column is repeated and to normalize this you should look at the example of MGA.

Thirdly the "Metal layer" column is a string and not a float value like the others.

So best to go for a table def like this:

<pre><code>create table yourTable(
 ID int primary key,
 ParamId int not null,
 Width float,
 Length float,
 Space float,
 Coupling float,
 Metal_layer varchar(20),
 Foreign key(ParamID) references Param(ID),
 Value varchar(20)
)
create table Param(
 ID int primary key,
 Name varchar(20)
)
</code></pre>

is this table any good for mysql? I wanted to make it flexible in the future for this type of data storage. With this table structure, you can't use a PRIMARY KEY but an index ... 

Should I change the format of the table to have headers - Primary Key, Width, Length, Space, Coupling ... 

<pre><code>ID_NUM Param Value
1 Width 5e-081
1 Length 12
1 Space 5e-084
1 Coupling 1.511
1 Metal Layer M3-0
2 Width 5e-082
2 Length 1.38e-061
2 Space 5e-081
2 Coupling 1.5
2 Metal Layer M310
</code></pre>

mysql table structure proposal?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

这个表对mysql有什么好处吗？我想让这种类型的数据存储在未来变得灵活。在这种表结构中，您不能使用主键，而可以使用索引...我是否应该将表格的格式更改为具有标题-主键、宽度、长度、空格、耦合...ID_NUM  Param   Value1   Width   5e-0811   Length  121   Space...

问mysql表结构建议？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql表结构建议？EN