CODEWITHSUNDEEP
mysqlsqlphpmyadminxampp
Sam Genest
Sam Genest

Reputation: 117

Problems with MySQL DataTypes

I'm having a problem with several tables I'm trying to enter into an Xampp database. I keep on getting "out of column bounds" errors (I'm interpreting this as meaning I've declared a DataType with too large a size). I'm using w3schools page for the appropriate sizes (http://www.w3schools.com/sql/sql_datatypes.asp). According to the site:

TINYINT can hold numbers in a range of 0 to 225 if declared as unsigned

BIGINT can hold numbers in a range of 0 to 18446744073709551615 if declared as unsigned

DOUBLE does not have a range listed

What I'm looking for is an example (I've looked through a lot of websites and a MySQL book and haven't found any) of declaring these in their largest form or some clarification as to how to how declaring these and similar dataytypes work. My current understanding is that:

TINYINT(size) where size can be as great as 127 if signed and 225 as unsigned EX: 

tinyint_var TINYINT(127)

or

tinyint_unsigned_var TINYINT(225) UNSIGNED

BIGINT(size) where size can be as great as 9223372036854775807 if signed or 18446744073709551615 as unsigned

biginT_var BIGINT(9223372036854775807)

or

bigint_unsigned_var BIGINT(18446744073709551615)

DOUBLE(size, decimalplaces) not sure what the bounds are on this clarification apreciated.

I'm getting errors with BIGINT:

bigint_var BIGINT(1000000000)

and with DOUBLE:

double_var DOUBLE(1000, 2)

Thanks for any help!

Upvotes: 0

Views: 329

Answers (2)

Deji
Deji

Reputation: 777

TINYINT is a 1-byte value. It's range is 0-255, so 3 would be the maximum display width, etc.

Upvotes: 0

ruakh
ruakh

Reputation: 183321

You seem to have misunderstood the SQL notation for declarations. This:

tinyint_var TINYINT(127)

is trying to declare a TINYINT with display-width of 127 digits (see http://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html). Since, as you've read, a TINYINT only holds values up to, at most, 255, its values can't be anywhere near 127 digits long, so a display-width of 127 digits is not allowed.

(And, similarly for your other examples.)

Upvotes: 1

Related Questions