![]() too conservative:while nothing will break from being too conservative with data types, you will be wasting storage space.For example, we’ll get errors if we have a sale with an amount_paid of $100.00 or more or an agent_comment that is longer than 65 characters too constrained: MySQL will give errors when you try to input values that are larger than what the data type allows.For example, having purchase_time as a string and not a time data type means you won’t be able to perform time operations (such as calculating how long it’s been since the purchase) on the field wrong data type: you won’t be able to use the field in the way that you would expect.The issues we encounter with the data types MySQL automatically selected are: For example, letting MySQL select the data types for a sales table with the sample values shown previously gives several issues. However, this will rarely give you the best data type selection. You could let your database select data types for you when creating your table with sample records. Thus, a better selection is MEDIUMINT unsigned, which should cover us for the next several years. When we go over 65,535, the SMALLINT will no longer be sufficient. However, we expect to get to 100,000 customers over the next 6-12 months. Using the MySQL integer data type table from the previous section, we may be tempted to select SMALLINT unsigned as the data type, since that’s the smallest data type that will accept our current integer values from 0 to 15,000. Today our fictional company only has 15,000 customers. The basic strategy for selecting the best data type is to select the smallest data type that matches the kind of data you have and that allows for all the feasible values of your data.įor example, customer_id in our sample sales table is a whole number starting with 0. For example, MySQL provides these possible integer data types: Most databases offer size variations for each type. Some databases (like MySQL) don’t have boolean data type and instead convert boolean values into integers (1=TRUE, 0 = FALSE) boolean: for boolean (true/false) values. ![]() varchar(n): for variable-length character strings, where the value in the parenthesis dictates the maximum accepted size of each entry.character(n):for fixed-length character strings, where the value in the parenthesis dictates the fixed size of each entry.timestamp / datetime: for date and time values.Commonly used for all real numbers except monetary fields Can be signed (allow positive and negative values) or unsigned (only allow positive numbers). float / doubles: for numbers with fractions not requiring exact precision.The user specifies the number of significant digits allowed overall (x) and after the decimal point (y) in the parentheses decimal(x,y): for numbers with fractions requiring exact precision.Commonly used for ID fields and counts of something ![]() ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |