MySQL database technology (09)

  2.3 choose the type shown in 
  On a description of the various options for MySQL column types and their properties, and they can store the various values, the storage space occupied by, and so on.    But in practice, how to create a table which decided to use this type »    Discussed in this section should be considered before a decision is made of various factors.    The most "common" type is out of the string type.    Can be any string of data storage, because the number and date can be expressed in the form of string.    But why are not all out for the definition of string to the end of the discussion here? »    Let us look at a simple example.    Some look like the assumption that the number of value.    They can be said for the string, but should do so? »    To do so what would happen » 
  Is an inevitable matter, it is possible to use more space, because a string, the number of storage more effective.    We may have noticed, because a few strings and the different approach, the query results to be different.    For example, the sort with a few strings on the order is different.    2 less than the number 11, but the string "2" by order of the dictionary more than "11."    Numerical content available following the presentation to understand this issue: 
MySQL database technology (09) (a)
  Will be added to the list is mandatory that a numerical, but this reasonable? »    General may be unreasonable.    As shown in the few rather than the series has several important implications.    It listed the value of each string to the implementation of the conversion, this is inefficient.    And the column values converted to prejudice the results of the use of MySQL out on the index lower after a query speed.    If these values as a start value is stored, then these properties on the reduction will not appear.    That instead of using a a simple choice is not simple, its storage needs, for efficiency and performance and other aspects will have an important impact. 
  In front of examples to illustrate, the choice of the type shown, it is necessary to consider the following questions: 
  â–  out what type of storage in the value »    This is an obvious problem, it must be determined.    Can be any type of value for that series, especially when the value of the use of more appropriate type may get better performance when (date and time values as well).    Clearly, the value to be dealt with the type of assessment may not necessarily be a trivial matter, especially in the data is more so when other people's data.    If you are for others to create a table column to understand the value of the type of storage is extremely important, we must mention the issue enough to get the decision-making adequate information. 
  â–  out of a specific range? »    If they are rounded, they are always non-negative? »    If so, can be UNSIGNED type.    If they are strings, the total value of fixed-length from their focus on selected? »    If so, ENUM or SET is the appropriate type.    Type in the range of storage and use of a compromise between.    To have more than one "big" type »    For a few, if its range is limited, can choose the type of smaller, almost unlimited range of a few, should choose the larger type.    The series, so that they can also make them short length, but if you want to store only the value of less than 10 characters, should not be optional CHAR (2 5 5). 
  â–  performance and efficiency of what the question is «    Some types than some other types of high efficiency.    Numerical computing in general faster than the string of operations.    Shorter than the long string string running faster, smaller and disk consumption.    Longer than the type of variable types of better performance. 
  â–  hope that the value of what kind of comparison »    The series, the comparison is case-sensitive, can not case sensitive.    Their choice will affect sort, because it is based on a comparison. 
  â–  out a plan to index them »    If you plan to index the list, then it will affect you out type of choice, because some MySQL version does not allow for certain types of index, for example, can not BLOB and TEXT types index.    Some MySQL version requirements and definition of the index as a NOT NULL, so that you can not use NULL value. 
  Let us now to a more detailed consideration of these issues.    Here like to point out is: in a table, would like to make the best possible choice of the type shown, but if the choice is not the best of its actual, it will not bring much of the problem.    A LTER TABLE available the original choice of the type of conversion for a better type.    Found that the data contained in value than the original vision of large, it can be like SMALLINT MEDIUMINT as simple as replacing the type to be replaced.    This change may also sometimes very complicated, for example, CHAR-with a specific type of replacement-value types of ENUM.    In MySQL 3.23 and subsequent versions, can be used PROCEDURE ANALYSE () to obtain the information listed, such as minimum and maximum coverage and are recommended in the value of the range of the best types.    This will help determine the use of smaller type, thus improving the enquiries related to the performance table, the table and reduce storage space for the required amount. 
  2.3.1 out what type of storage in value 
  In determining the type of column, it should first consider the type of column values, because the choice of this type, with the most obvious meaning.    Usually, a few numerical series of storage, serial storage in the series, the date and time are stored in the date and time.    If the numerical part of a minority, then should not the type of floating-point out integer type, and so on.    Sometimes there are exceptions, not to generalize.    Is to choose the type of meaningful, we should understand the characteristics of the data used.    If you plan to store their data, they probably will be on how to store their own good idea.    However, if other people ask you to 
  They built a table and decided to type out sometimes very difficult.    This does not look like to manage their data so easy.    Should be adequate to ask questions, understand the actual sheet should include the type of value. 
  If someone tells you, out of a need to record "rainfall."    It was a few? »    Or its "main" is a value, that is, generally but not always encoded into a few? »    For example, in watching television news, weather reports normally include rainfall.    Sometimes a few (such as "0.2 5"-inch of rain), but sometimes a "minor (trace)" rainfall, which means "rain is not big."    This weather forecast is very appropriate, but stored in the database how »    There may need to "trace" of a few quantitative and numerical set out to use the type to record rainfall, may require the use of string so that it can record "trace" the word.    Or can raise some more complex arrangements, using a numerical list and a serial, if let out another one filled with a NULL.    Obviously, if possible, should avoid this final choice, this option so that the final form difficult to understand, so that enquiries more difficult.    We generally try to store all the numerical form of the line, but only to show that they only need to convert.    For example, if less than 0.01 inches of rainfall was considered as non-zero trace, you can choose out of the following: 
MySQL database technology (09) (b)
  For the money, the need to address yuan and the division.    This seems like the floating-point value, but FLO AT DOUBLE and prone to rounding error, in addition to just generally accurate records, these types may not be suitable.    Because people of their money are very sensitive, it is best used to provide a complete accuracy of the type, for example: 
  â–  that the money for DECIMAL (M, 2) type, choose M is suitable for the required range of the maximum width.    This is a floating-point precision of two decimal value.    DECIMAL is to the advantage of value is expressed as a string, and not prone to rounding error.    Disadvantage is the string of internal storage computing than on the value of the poor efficiency of operations. 
  â–  in the internal integral type that all the money value.    The benefits are integral to internal use, it will very quickly.    Disadvantage is the need to import or export or use by 100 pairs of value addition to the conversion.    Some data is clearly value, but must decide the use of floating-point type or the use of type Integer.    Should be clear about the use of units and the need for what is what kind of accuracy.    The accuracy of the entire unit is enough? »    Or require that the minority of the unit? »    This will help you float out and rounded out a distinction between.    For example, if you are that weight, then the record if the value of a pound, you can use a plastic out.    If you want to record some of the minority, they should use floating-point out.    In some cases, even the use of multiple fields, such as: if you want pounds and ounces under the weight of record, you can use more than one row. 
  Height (height) is another type of values, said several methods are as follows: 
  â–  such as "six feet two inches" can be expressed as "6 – 2," a string.    This form is easy to see and understand the advantages (of course, than "74 inches better understanding"), but the value it is very difficult for math, or if the sum for the average. 
  â–  a numerical field that feet, another field that numerical inch.    This numerical computing that is relatively easy, but the two fields difficult to use than a field. 
  â–  only one that the value of inches.    This database is the most easy way, but this way the meaning clear.    But remember, do not necessarily need to use your usual format to express the kind of value.    M y SQL can be a function of conversion will look for the obvious significance of the value.    Therefore, the conclusion that this method is highly likely the best way. 
  If you need storage date information, including the time it needs »    That is, they always need to include the time it »    MySQL does not provide optional part of the time period type: D ATE may include time, and D ATETIME must include time.    If time really is optional, then use a D ATE out record date, a TIME record time out.    TIME as allowing NULL and interpreted as "no time": 
MySQL database technology (09) (c)
  Based on the information to date in the main – a breakdown of relations linking two tables, need time to decide whether or not the value of particular importance.    If you are a research, including access to some of your office to test the topic.    In a standard set of preliminary testing, you may be on the same day a few additional tests, depending on the choice of the initial test results may be.    You may use a master – a breakdown of that relationship to the information, including identification information and the subject of the preliminary testing standards stored in a main records, and other tests for the preservation of supporting breakdown of the line.    Based on the topic of ID and then to test the date of this connected to the two tables together. 
  In such a case to answer the question is, can only date, or whether it is necessary to use both the date and time.    This problem depends on whether a topic on the same day in the testing process more than once.    If so, then the time should be recorded (for example, record the beginning of the testing process), or use D ATETIME out, or were used 
  DATE and TIME column (both required).    If a subject of a two day test, no time value can not be the subject of the breakdown of proper records and records of the main association. 
  I have heard some people claimed that "I do not need the time I have never been on the same day that a test of the two."    Sometimes they are correct, but I also saw the entry of these people later the same day a number of test subjects after the data, in turn, consider how to prevent the breakdown of the wrong records and the main mixed record.    Unfortunately, at this time too late!    Sometimes in the form of increased TIME out to deal with this problem, unfortunately, unless there is some independent data sources, such as the original written records, it is difficult to collate existing records.    In addition, the breakdown of record can not eliminate the ambiguity in order to fit their relevance to the main record.    Even if an independent source of information, and to do so is very chaotic, is likely to have been prepared to take advantage of problems with the application form.    Preferably to the owner describe the problem and to ensure the creation of the table before them a very good description. 
  Sometimes with some incomplete data, which would interfere with the choice of the type shown.    If the conduct genealogical research, need to record date of birth and death dates, can sometimes be found in the data collected by only a person of the year of birth or death, but no exact date.    If you use D ATE out, unless there is a complete date of value, they can enter a date.    If you want to record with any information, even if no integrity preserved, then it may be necessary to preserve the independence of the year, month, day field.    This will enter the date of the members and not as part of NULL.    In MySQL 3.23 and subsequent versions, also allows the D ATE, and 0 or on the part of 0.    This "fuzzy" can be used to date indicated that he did not complete the date value. 
  2.3.2 out of a specific range? 
  If you have already decided to choose a generic categories listed on the type, then want to consider that the value of the range will help your choice down to the category of specific types.    If the hope that the store integer value.    These values of the entire range of 0 to 1 0 0 0, then you can use to BIGINT SMALLINT from all types.    If the value of the entire range of up to 2 000 000, it can not use SMALLINT, the scope of their choice from MEDIUMINT to BIGINT.    This may need to select the choice of a type.    Of course, you can simply want to store the value of the largest choice of the type (such as the above example, select BIGINT).    However, the general should have to choose the value of storage, enough for storing it the smallest type.    To do so, you can minimize the use of storage table, the best performance, because usually the smaller out more out of treatment quickly.    If we do not know what to say that the value of the range, then the need for speculation or use BIGINT to meet a worst-case scenario.    (Please note that if the use of a guess the type is too small, will not be Baizuo; after A LTER TABLE can use this column to read some of the larger types.) 
  In Chapter 1, we save credits for the programme has created a score sheet, it has a record of tests and test score out of the credits.    In order to discuss the sake of simplicity, the use of the table to create the type INT, but now we can see that, if the credits in the 0-100 range, the better choice should be TINYINT UNSIGNED, because the use of smaller storage space.    The data are also affected range of types of attributes.    If the data is never negative, you can use UNSIGNED attributes, otherwise it can not be used. 
  No numerical string type, as shown in the "range", but they have length, need to know the strings can be used out of the maximum length.    If the string less than 2 5 6 characters, you can use CHAR, VA RCHAR, TINYTEXT or TINYBLOB, and other types.    If you want a longer series, you can use TEXT or BLOB type, and CHAR and VARCHAR is no longer options.    For that to set a fixed value of the serial, consider using ENUM or SET out type.    They may be very good option, because they are using in-house to express a few.    These two types of operation is of value and, therefore, than other types of string more efficient.    They also strings than other types of compact, space-saving.    In describing the need to address the value of the range, the best term is "always" and "never" (such as "always less than 1 0 0 0" or "not negative"), because they can more accurately And are bound by the choice of type.    But not confirmed prior to the two anti-term.    In particular with other people talk about their data, and they started indiscriminate use of these two terms, we should pay attention.    Someone said, "always" or "no", they must understand that this is the exact meaning.    Sometimes people say that their data is always a particular nature and its true meaning is "almost always." 
  For example, if you for some people to design a table, and they tell you, "We always test credits 0到1 0 0."    According to this description, you choose the type and make it TINYINT for UNSIGNED, because the value is always non-negative.    However, you find that encoded the time of admission to the database of people sometimes use – 1 to that "students absent due to illness."    Ah, they did not tell you this.    NULL may be used to express 1, but if not, must be recorded – 1, this can not be used UNSIGNED out of the (A LTER TABLE had to use to remedy!).    Sometimes the discussion on these cases can be passed to some simple questions to simplify, such as: there was an exception? »    If there was an exception, even if only once, must also be considered.    You will find that your discussion database design and the people always think that if exceptions do not often happen, then no relationship.    However, in the creation of the database, we can not think of.    The problem is not required to appear more frequent exceptions, but there are no exception »    If so, must be taken into account. 
  2.3.3 performance and efficiency of the 
  The choice of the type shown in several areas affected query performance.    If the next few remember the section on general guidelines, will be able to elect to deal effectively with MySQL table column type. 
  1. String of computing and numerical 
  Numerical computing in general faster than the string operation.    For example, compare operator, in a single operation in a few comparisons.    The string operation involving several case-by-byte comparison, if the string longer the case, the number of such comparisons.    If the serial number of limited value, or SET ENUM should be used to obtain the type of numerical superiority of computing.    These two types of in-house is that the number may be more effective for treatment.    For example, the replacement of that series.    Sometimes that can be used to a few Chuanzhi to improve its performance.    For example, to use at the four-digit points (dotted – quad) said that IP law, such as 1 9 2.1 6 8. 0.4, you can use the string.    But can also use four bytes of each byte UNSIGNED types of storage for each of the four-digit, will be converted to IP, rounded form.    This can save space that can accelerate the speed to find.    But on the other hand, IP said, for the INT value will find a sub-such as the number of such pattern matching difficult to complete.    Therefore, we can not only consider the question of space; need to use these values to decide what to do that better. 
  2. The type of smaller and larger type 
  Smaller type than the type of treatment to more much faster.    First, they take less space, and activities related to the disk costs less.    The series, the processing time and directly related to the length of string.    Under normal circumstances, the smaller form processing faster, because the need for handling the disk I / O less.    The types of fixed-length column, should choose the smallest type, can be stored for as long as the scope of the value you.    For example, if MEDIUMINT enough, do not choose BIGINT.    If we only need FLO AT accuracy, we should not choose DOUBLE.    The variable type, and still be able to save space.    A BLOB types of value with two bytes of the record length, and LONGBLOB used a 4-byte record the length of its value.    If the stored value does not exceed the length forever 6 4 KB, BLOB will use two bytes for each value of savings (of course, TEXT can do similar type of consideration). 
  3. The long and variable type 
  Longer than the general type of variable type of treatment at a faster pace: 
  â–  For variable series, records of different sizes, in its on and remove many changes will make additional debris in the table.    OPTIMIZE TABLE need to run on a regular basis to maintain performance.    Which are no longer the issue. 
  â–  table in the event of the collapse, the long list of the table easy to re-structure, because the beginning of each record is determined by location.    Variable long list of the absence of such facilities.    This is not a query processing and related performance problems, but it certainly can speed up the restoration process table.    If a variable in the table are long, they will be converted to the long list of performance can be improved, because fixed-length records easier to handle.    In an attempt to do so, should consider the following questions: 
  â–  use of the long list of a compromise.    They are faster, but the space occupied by the more.    CHAR (n) of the type shown in the total value of each occupying n bytes (even if the string is empty so), because in the table storage, the value of the length of the right not to fill in the spaces.    The VARCHAR (N) type of out of less space, because they are only allocated to each of the required storage space, each with a value of bytes used to record its length.    Therefore, if the CHAR and VARCHAR out to choose between the need for time and space to make a compromise.    If the speed is the main concern of factors, the use of CHAR out to get the long list of performance advantages.    If space is the key, should be used VARCHAR column. 
  â–  can not just convert a long list of variable; need to convert them all.    And must use a phrase and all ALTE RTABLE conversion, or conversion will not work. 
  â–  sometimes can not use fixed-length type, so do not even want to visit.    For example, than 255 characters long string, no fixed-length type. 
  4. Types can be indexed 
  Enquiries can speed up the rate of the index, therefore, should select the type of index. 
  5. NULL and NOT NULL type 
  If the definition as a NOT NULL, its processing faster, because MySQL query processing do not have to check the column values to ascertain whether it NULL, the table can save one per line.    To avoid a NULL column can make enquiries more simple, because there is no need to NULL as a special case to consider.    Usually, for the simpler, quicker handle on.    Presented by the performance criteria are sometimes contradictory.    For example, according to MySQL on the line in terms of positioning, including CHAR set out the long line than VARCHAR column contains the variable-line processing faster.    But on the other hand, it will also take up more space, it will lead to more disk activity.    From this point of view, VARCHAR may be faster.    Experience as a rule, may be assumed that the long list can improve performance, even if it takes more space so.    The key for a particular application, may want to be long and variable realization of a long table in two ways, and some testing to determine which way to your specific applications faster. 
  2.3.4 hope that the value of what kind of comparison 
  According to the definition of string, the string type to be case-sensitive or not case-sensitive way to compare and order.    Table 2-14 show a case-insensitive and its equivalent for each type of case-sensitive type.    According to the definition of out not given keyword BINA RY, some types (CHAR, VA RCHAR) is a binary code or binary code.    Other types (BLOB, TEXT) "binary" type in the name implied. 
MySQL database technology (09) (4)
  Please note that the binary (case sensitive) type only in comparison and sort of different from the corresponding non-binary (not case sensitive) type.    Any type of string can contain any type of data.    In particular, TEXT types despite the type of column called "TEXT (text)", but it can make good binary data storage.    If you want to use a comparison is case-sensitive and can not distinguish the case out.    In the hope that a case-insensitive comparison, the mandatory use of BINA RY keyword string as a binary Chuanzhi.    For example, if a CHAR my_col out, may be different ways to compare: 
  my_col = "ABC" is not case-sensitive 
  BINARY my_col = "ABC" case-sensitive 
  my_col = BINARY "ABC" case-sensitive 
  If there is a hope that the non-dictionary to order storage Chuanzhi, may consider the use of ENUM out.    ENUM value of the order is the basis of the definition set out in the enumeration value of the order, so that these values can be in any desired order of ranking. 
  2.3.5 plan to index the list? 
  The use of the index can more effectively deal with enquiries.    Index options is the first four chapters of a theme, but the general principle is used to select WHERE clause of the line out for the index.    If you want to be indexed on a column or are included in the multi-out index, in the types of choices may be limited.    As early as in 3.23.2 version of the MySQL release, the index out to be defined as NOT NULL, and not the type BLOB or TEXT index.    These limitations in MySQL 3.23.2 version are withdrawn, but if you are using an earlier version, unable or unwilling to upgrade, then we must comply with these constraints.    But in the following circumstances, we can bypass them: 
  â–  If you can specify a value as a special value, so can be used as NULL and treated the same things.    For DATE out, you can specify the "0000 – 00 – 00" that "no date."    In tandem, can specify empty string on behalf of "indispensable value."    In numerical column, are generally only if the storage of non-negative, you can use – 1. 
  â–  can not BLOB or TEXT types of indexing, but if not more than 255 strings at it, you can use equivalent VARCHAR column types and their index.    Can VARCHAR (255) BINARY for BLOB value, VARCHAR (255) for the TEXT value. 
  2.3.6 choice of the type shown in the interrelated degree 
  Do not think that type of option is out independent of each other.    For example, the value of the range and size of the store; increase in the value of the scope, the need for more storage space, it would affect performance.    In addition, consider the option of using AU TO _INCREMENT to create a unique serial number stored out what the meaning.    The choices are few results, they are involved in the type of index and the use of NULL, is listed as follows: 
  â–  AU TO_INCREMENT is an integral type should be used only for the listed properties.    It will limit your choices in TINYINT to BIGINT above. 
  â–  AU TO_INCREMENT column should be indexed to the largest serial number can be quickly identified, do not need to scan all the table.    In addition, in order to prevent reuse by serial number, index, must be unique.    This means that the definition must be shown for the PRIMA RY KEY or defined as UNIQUE index. 
  â–  If used as early as MySQL version 3.2 3.2, the index can not contain NULL values are, therefore, must be defined as NOT NULL.    All of this said, not only such as the following definition of an AU TO_INCREMENT out: 
MySQL database technology (09) (5)
  The use of AU TO_INCREMENT Another result is that because it is used to generate a positive sequence, so it would be better defined as the AU TO_INCREMENT out UNSIGNED: 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Tags: ,