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 integers: for numbers without fractions.What are the Possible Data Types?ĭifferent databases have different data types available, but most fit into these categories: The kind of data (integers, text, real numbers, etc…) and the possible value ranges (0 to 1,000 any 3 characters etc…) correspond to specific database data types. Has_discount: boolean where entries can be TRUE or FALSE notes: text, where the entry can be as long as what is allowed in our agent input tool (250 characters) Sale_id: integer values incrementing by one for every new saleĬustomer_id: integer values incrementing by one for every new customerĬurrency: text always in the 3-character currency codeĪmount_paid: monetary real numeric values between $0.00 and $1,000.00ĭevice: text, where the values can be: ‘desktop’, ‘mobile app’, and ‘mobile web’ What are Data Types?ĭata types define what kind and range of data can be stored in a given field (or column).Ĭonsider a sales table with these sample records:Įach field has its own type and range of values: This tutorial will explain how to select the right data types. A key factor in determining the size of your data is the data type you select. The size of your data doesn’t just impact storage size and costs, it also affects query performance. As a typical company’s amount of data has grown exponentially it’s become even more critical to optimize data storage.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |