[SOLVED] MySQL Data Type

[oceans]

Dear People,

 

Please refer me to a web page where I can get information on MySQL DataTypes. (Please refer me to Tables, NOT manuals, you would know why).

 

I am looking for (5) information particularly.

 

(Additionaly please comment as well, thanks.)

 

(1) When it comes to number variables, I wish to know the max value for each type. I noted there are a lot more type(s) over here then I used to work in ODBC; further, I am confused, when in defining number variables, MySql requests for length, it should go by max value only right.

 

(2)I wish to know the difference between VARCHAR and TEXT.

 

(3)What is SET, BLOB, ENUM

 

(4) What is Collation

 

(5) What is max length for a row, I row hs very long eg. 3 X 1700 bytes ++

 

I have a system running already, I now want to put exect type of variables

 

Thanks.

 

 

[kathas]

For god couldn't you type those in google...?

 

It would take you less time...

 

http://dev.mysql.com/doc/refman/5.0/en/data-types.html

 

google search: http://www.google.com/search?hl=en&q=MySQL+Data+types&btnG=Google+Search

[oceans]

Thanks mate,

 

I should have 

[oceans]

Friend,

Since you arround,

please advice, how should i understand "length" for a nuber feild, i could not get it,

at present i just put a big number and run, but now i have to define strictly

[kathas]

#

 

SMALLINT[(M)] [uNSIGNED] [ZEROFILL]

 

A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.

#

 

MEDIUMINT[(M)] [uNSIGNED] [ZEROFILL]

 

A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

#

 

INT[(M)] [uNSIGNED] [ZEROFILL]

 

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

#

 

INTEGER[(M)] [uNSIGNED] [ZEROFILL]

 

This type is a synonym for INT.

#

 

BIGINT[(M)] [uNSIGNED] [ZEROFILL]

 

A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

 

It is in the link... and there is also tinyint(0,1)

[oceans]

Dear Kathas,

 

I thank you again, I am reading the link,

 

I am using WAMP.

 

What I ment was, when I define my MySQL variable, e.g. INT, the next column it asks for length, that was what I ment, thanks.

[kathas]

i guess you are using phpMyAdmin the next column is the length...

 

If you leave it blank the default is 11 i think...

I believe this is the maxbyte size of the value... Integer has 4 so 11 is bigger than that and you won't have problems...

 

BIGINT takes up 8 bytes instead of INT that takes 4...

 

I tried to find an official page that says those stuff but i couldn't they were only implied...

 

Kathas

[oceans]

Thanks,

 

I will leave it blank when it comes to numbers, as for varchar, I have to define,

 

Thanks friend.

[MasterACE14]

I guess this is what you are looking for, (this is straight from my PHP 5 text book)

 

MySQL Data Types

 

int - An integer from -2147483648 to 2147483647

 

decimal - A floating point number that can specify the number of permissable digits. For example decimal(3,2) allows -999.99 to 999.99

 

double - A long double-precision floating point number

 

date - A date in the YYYY-MM-DD format

 

time - A time in the HH:MM:SS format

 

datetime - A combined date and time in the format YYYY-MM-DD HH:MM:SS

 

year - A year 1901-2155 in either YY or YYYY format

 

timestamp - Automatic date and time of last record entry

 

char() - A string of defined fixed length up to 255 characters long. For example, char(100) pads a smaller string to make it 100 characters long

 

varchar() - A string of defined variable length up to 255 characters long that is stored without padding

 

text - A string up to 65535 characters long

 

blob - A binary type for variable data

 

Hot Tip: An enum type can contain up to 65535 permissible elements.

 

enum - A single string value from a defined list. For example, enum("red,"green","blue") allows entry of any one of these three colors only

 

set - A string or multiple strings from a defined list. For example, set("red","green","blue") allows entry of one or more of these three colors

 

MySQL Field Modifiers

 

not null - Insists that each record must include data entry in this column

 

unique - Insists that records may not duplicate any entry in this column

 

auto_increment - Available only for numeric columns to automatically generate a number that is one more then the previous value in that column

 

primary key() - Specifies as its argument the name of the column to be used as the primary key for that table. For example, primary key(id)

 

Hope this Helps. ACE

[oceans]

Dear Friend,

 

Thanks, I think i will give an example, that way I can express myself better.

 

Ok, in phpMyAdmin, I am creating a column,

 

I am put a BlaBla in Field , TINYINR in Type, "What to put here?" Length/Values, "What is?" Collation.

 

Thanks.

 

If you fill in for me, I will understand, I think.

[oceans]

Adding to that may I know what is the diff between

"Unsigned" and " Unsigned Zerofill", it dif appreas to be subtle, I can't catch it.

[btherl]

"When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width."

 

Taken from http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

 

Note that zerofill affects display only, not storage.

[oceans]

Thanks People.

I got it