Is there a list of best practice MySQL data types for common applications. For example, the list would contain the best data type and size for id, ip address, email, subject, summary, description content, url, date (timestamp and human readable), geo points, media height, media width, media duration, etc
是否有常见应用程序的最佳实践MySQL数据类型列表。例如,列表将包含id,ip地址,电子邮件,主题,摘要,描述内容,URL,日期(时间戳和人类可读),地理点,媒体高度,媒体宽度,媒体持续时间等的最佳数据类型和大小,等等
Thank you!!!
3 个解决方案
#1
8
i don't know of any, so let's start one!
我不知道,所以让我们开始吧!
numeric ID/auto_increment primary keys: use an unsigned integer. do not use 0 as a value. and keep in mind the maximum value of of the various sizes, i.e. don't use int if you don't need 4 billion values when the 16 million offered by mediumint will suffice.
numeric ID / auto_increment主键:使用无符号整数。不要使用0作为值。并记住各种大小的最大值,即如果你不需要40亿的值,当mediumint提供的1600万就足够时,不要使用int。
dates: unless you specifically need dates/times that are outside the supported range of mysql's DATE and TIME types, use them! if you instead use unix timestamps, you have to convert them to use the built-in date and time functions. if your app needs unix timestamps, you can always convert the standard date and time data types on the way out using unix_timestamp().
日期:除非您特别需要超出mysql的DATE和TIME类型支持范围的日期/时间,否则请使用它们!如果你改为使用unix时间戳,你必须将它们转换为使用内置的日期和时间函数。如果您的应用程序需要unix时间戳,您可以使用unix_timestamp()始终在出路时转换标准日期和时间数据类型。
ip addresses: use inet_aton() and inet_ntoa() since it easily compacts an ip address in to 4 bytes and gives you the ability to do range searches that utilize indexes.
ip地址:使用inet_aton()和inet_ntoa(),因为它可以轻松地将ip地址压缩为4个字节,并使您能够进行利用索引的范围搜索。
#2
6
Integer Display Width You likely define your integers something like this "INT(4)" but have been baffled by the fact that (4) has no real effect on the stored numbers. In other words, you can store numbers like 999999 just fine. The reason is that for integers, (4) is the display width, and only has an effect if used with the ZEROFILL modifier. Further, this is for display purposes only, so you could define a column as "INT(4) ZEROFILL" and store 99999. If you stored 999, the mysql REPL (console) would output 0999 when you've selected this column.
整数显示宽度您可能会将此类整数定义为“INT(4)”,但却被(4)对存储的数字没有实际影响的事实所困惑。换句话说,你可以存储像999999这样的数字。原因是对于整数,(4)是显示宽度,并且只有与ZEROFILL修改器一起使用时才有效。此外,这仅用于显示目的,因此您可以将列定义为“INT(4)ZEROFILL”并存储99999.如果存储了999,则当您选择此列时,mysql REPL(控制台)将输出0999。
In other words, if you don't need the ZEROFILL stuff, you can leave off the display width.
换句话说,如果你不需要ZEROFILL的东西,你可以不用显示宽度。
#3
4
Money: Use the Decimal data type. Based on real-world production scenarios I recommend (19,8).
资金:使用十进制数据类型。根据我推荐的实际生产场景(19,8)。
EDIT: My original recommendation was (19,4); however, I've recently run into a production issue where the client reported that they absolutely needed decimal with a "scale" of "8"; thus "4" wasn't enough and was causing improper tax calculations. I now recommend (19,8) based on a real-world scenario. I would love to hear stories needing a more granular scale.
编辑:我原来的建议是(19,4);然而,我最近遇到了一个生产问题,客户报告说他们绝对需要十进制的“比例”为“8”;因此“4”是不够的,导致不正确的税收计算。我现在推荐(19,8)基于真实场景。我很想听听需要更细粒度的故事。
#1
8
i don't know of any, so let's start one!
我不知道,所以让我们开始吧!
numeric ID/auto_increment primary keys: use an unsigned integer. do not use 0 as a value. and keep in mind the maximum value of of the various sizes, i.e. don't use int if you don't need 4 billion values when the 16 million offered by mediumint will suffice.
numeric ID / auto_increment主键:使用无符号整数。不要使用0作为值。并记住各种大小的最大值,即如果你不需要40亿的值,当mediumint提供的1600万就足够时,不要使用int。
dates: unless you specifically need dates/times that are outside the supported range of mysql's DATE and TIME types, use them! if you instead use unix timestamps, you have to convert them to use the built-in date and time functions. if your app needs unix timestamps, you can always convert the standard date and time data types on the way out using unix_timestamp().
日期:除非您特别需要超出mysql的DATE和TIME类型支持范围的日期/时间,否则请使用它们!如果你改为使用unix时间戳,你必须将它们转换为使用内置的日期和时间函数。如果您的应用程序需要unix时间戳,您可以使用unix_timestamp()始终在出路时转换标准日期和时间数据类型。
ip addresses: use inet_aton() and inet_ntoa() since it easily compacts an ip address in to 4 bytes and gives you the ability to do range searches that utilize indexes.
ip地址:使用inet_aton()和inet_ntoa(),因为它可以轻松地将ip地址压缩为4个字节,并使您能够进行利用索引的范围搜索。
#2
6
Integer Display Width You likely define your integers something like this "INT(4)" but have been baffled by the fact that (4) has no real effect on the stored numbers. In other words, you can store numbers like 999999 just fine. The reason is that for integers, (4) is the display width, and only has an effect if used with the ZEROFILL modifier. Further, this is for display purposes only, so you could define a column as "INT(4) ZEROFILL" and store 99999. If you stored 999, the mysql REPL (console) would output 0999 when you've selected this column.
整数显示宽度您可能会将此类整数定义为“INT(4)”,但却被(4)对存储的数字没有实际影响的事实所困惑。换句话说,你可以存储像999999这样的数字。原因是对于整数,(4)是显示宽度,并且只有与ZEROFILL修改器一起使用时才有效。此外,这仅用于显示目的,因此您可以将列定义为“INT(4)ZEROFILL”并存储99999.如果存储了999,则当您选择此列时,mysql REPL(控制台)将输出0999。
In other words, if you don't need the ZEROFILL stuff, you can leave off the display width.
换句话说,如果你不需要ZEROFILL的东西,你可以不用显示宽度。
#3
4
Money: Use the Decimal data type. Based on real-world production scenarios I recommend (19,8).
资金:使用十进制数据类型。根据我推荐的实际生产场景(19,8)。
EDIT: My original recommendation was (19,4); however, I've recently run into a production issue where the client reported that they absolutely needed decimal with a "scale" of "8"; thus "4" wasn't enough and was causing improper tax calculations. I now recommend (19,8) based on a real-world scenario. I would love to hear stories needing a more granular scale.
编辑:我原来的建议是(19,4);然而,我最近遇到了一个生产问题,客户报告说他们绝对需要十进制的“比例”为“8”;因此“4”是不够的,导致不正确的税收计算。我现在推荐(19,8)基于真实场景。我很想听听需要更细粒度的故事。