mysql---which type i should use? [closed]_问答_开发者

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so t开发者_运维问答hat it can be reopened, visit the help center. Closed 12 years ago.

when created the field for a table, i don't know which type (int, char,varchar....)and size i should use on the field. eg: email, time, address, phone....and so on.

Here's a general set of rules of thumb. Apply them in the order listed:

If it's a time or date, use a TIME or DATE type
If you're going to do math on it, use a numeric type such as INTEGER, FLOAT, REAL, or DECIMAL. Numbers such as telephone numbers or student IDs don't need to be numeric types, but some of them can be if you want. If you're going to add, subtract, multiply, or compare ranges of numbers, definitely make them numeric types. If you aren't going to then it probably doesn't need to be numeric. A good related rule of thumb is that if the number "00" is the same as the number "0" for your situation, then it should be numeric. If those are different numbers, (such as room numbers or student IDs in some cases) then it should not be a number. (I'll put rules of thumb for choosing which numeric type below.)
For everything else, use string types. Specifically, if you know the length either exactly or within a couple of characters, use a CHAR of that length.
If you don't know the length, use a VARCHAR and choose a reasonable maximum length. That is, something that you're sure would be long enough without getting silly with it. For instance, a VARCHAR(15) for last name just because none of your friends has a last name over 15 characters sounds find until you need to insert Mr. Schwietzer-McCullough. But a VARCHAR(100000) is probably overkill. For last names, something on the order of a VARCHAR(50) would probably be appropriate, but VARCHAR(100) would be reasonable.

Choosing numeric types:

If it's a whole number and will always be a whole number, chose an INTEGER type. Again, you'll have to choose size based on the largest value it could have. There's a page here: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html which includes a chart of largest values for the different sizes of INT types in MySQL.
If it's a decimal number such as money values, percents, temperatures, or most any ordinary decimal values, use DECIMAL. (NUMERIC means the same as DECIMAL in MySQL.) You'll choose two numbers to go along with this (such as DECIMAL(6,2)). The first number is the total number of digits to be stored. The second number is the number of digits to the right of the decimal place. That is, if you need to store prices of toothpaste which range from about $1 to $10, you could do DECIMAL(4,2) which would store numbers from 0.00 to 99.99. If you have to store stock prices which can include fractional cents and be quite high, you'd probably want DECIMAL(10,3) which would range from 0.000 to 9999999.999.
If you have numbers which need to be expressed in scientific notation and on which you'll be doing a lot of math and you need it to be efficient, use REAL or DOUBLE. Don't use these for storing prices or percents or stuff like that (see 2) because they'll introduce subtle inaccuracies. But if your answer could vary from 8x10^-5 to 2.58x10^34, then yeah, for stuff like that, use DOUBLEs. Be warned that these aren't actually stored in the form of X*10^Y, but rather X*2^Y because computers work in binary, so some numbers may be slightly different from their decimal representations. This is why we don't use them for currency.
If you've got a number like from 3, but you are really pressed for storage space, then use FLOAT. Really, in practice, you'll probably never use FLOAT. They're like DOUBLEs, but half as big.

Don't use integer for numbers, don't use varchar for variable width character lines. If you use a size smaller than the actual input you will save disk space by losing data.

Well, you can use varchar field for all the fields except for time. for time field you can use a datatype called time since this will help you in easy manipulation of the data.