I'm working on building a MySQL database that will have many related tables. I know I can create a primary key that is unique and auto increments, then reference that Primary Key as a Foreign Key in the related table. My question is what are best practices for Primary/Foreign key data types (int, varchar, etc..). I want to make sure I get this right the first time.
我正在构建一个包含许多相关表的MySQL数据库。我知道我可以创建唯一的自动增量主键,然后在相关表中将主键作为外键引用。我的问题是什么是主/外键数据类型(int,varchar等)的最佳实践。我想确保第一次就做对了。
Thank you
1 个解决方案
#1
11
In MySQL most commonly used datatype is INT UNSIGNED. It's 4 bytes long and can store numbers up to 4 billion which is more than enough for majority of applications. Using BIGINT (8B - 18446744073709551615 range) is usually an overkill when just starting with your application. Before your database gets too large for INT primary key, you'll need to hire a professional DBA anyway to help you with other issues.
在MySQL中,最常用的数据类型是INT UNSIGNED。它长4个字节,可以存储高达40亿的数字,这对于大多数应用来说已经足够了。从您的应用程序开始,使用BIGINT(8B - 18446744073709551615范围)通常是一种过度杀伤。在您的数据库因INT主键太大之前,您需要聘请专业的DBA来帮助您解决其他问题。
CHAR or BINARY datatypes can be used, when GUID type Primary key is used - such keys make it easier to shard data across multiple database instances, however with MySQL a multi master replication is more often seen.
当使用GUID类型主键时,可以使用CHAR或BINARY数据类型 - 这样的键可以更容易地跨多个数据库实例分片数据,但是对于MySQL,更常见的是多主复制。
When using InnoDB tables it's good to remember, that PK will be implicitly the last column in all other indexes created on specific table. This makes it important to use reasonably short datatype for PK (again 4B INT usually does its job well).
使用InnoDB表时,最好记住,PK将隐式地是在特定表上创建的所有其他索引中的最后一列。这使得为PK使用相当短的数据类型很重要(同样4B INT通常可以很好地完成它的工作)。
#1
11
In MySQL most commonly used datatype is INT UNSIGNED. It's 4 bytes long and can store numbers up to 4 billion which is more than enough for majority of applications. Using BIGINT (8B - 18446744073709551615 range) is usually an overkill when just starting with your application. Before your database gets too large for INT primary key, you'll need to hire a professional DBA anyway to help you with other issues.
在MySQL中,最常用的数据类型是INT UNSIGNED。它长4个字节,可以存储高达40亿的数字,这对于大多数应用来说已经足够了。从您的应用程序开始,使用BIGINT(8B - 18446744073709551615范围)通常是一种过度杀伤。在您的数据库因INT主键太大之前,您需要聘请专业的DBA来帮助您解决其他问题。
CHAR or BINARY datatypes can be used, when GUID type Primary key is used - such keys make it easier to shard data across multiple database instances, however with MySQL a multi master replication is more often seen.
当使用GUID类型主键时,可以使用CHAR或BINARY数据类型 - 这样的键可以更容易地跨多个数据库实例分片数据,但是对于MySQL,更常见的是多主复制。
When using InnoDB tables it's good to remember, that PK will be implicitly the last column in all other indexes created on specific table. This makes it important to use reasonably short datatype for PK (again 4B INT usually does its job well).
使用InnoDB表时,最好记住,PK将隐式地是在特定表上创建的所有其他索引中的最后一列。这使得为PK使用相当短的数据类型很重要(同样4B INT通常可以很好地完成它的工作)。