From Newsgroup: comp.databases.mysql

On 10/27/2019 6:09 AM, ^Bart wrote:

You use the size of variable you need. if the values in the age column
are really going to exceed the time to the heat death of the universe,
use a bigint

Ok, now I understood, I could use for the age also VARCHAR(3) because it means I can insert in a row till three numbers (I know VARCHAR is for
string but I could use also to insert numbers!) but the column's value
is "infinity" so I could have a column of millions and millions of
values but these values are till three items!

The mistake was to think to the columns and not to the value of the row!

Thanks!
^Bart

You do NOT want to store numeric data as strings. For one thing,
sorting will be crap. '2' will be larger than '15'.
--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

Jerry Stuckle <jstucklex@attglobal.net> wrote in news:qp4bqs$q6t$1@jstuckle.eternal-september.org:

On 10/27/2019 6:09 AM, ^Bart wrote:

You use the size of variable you need. if the values in the age
column are really going to exceed the time to the heat death of the
universe, use a bigint

Ok, now I understood, I could use for the age also VARCHAR(3) because
it means I can insert in a row till three numbers (I know VARCHAR is
for string but I could use also to insert numbers!) but the column's
value is "infinity" so I could have a column of millions and millions
of values but these values are till three items!

The mistake was to think to the columns and not to the value of the
row!

Thanks!
^Bart

You do NOT want to store numeric data as strings. For one thing,
sorting will be crap. '2' will be larger than '15'.

Agreed. If you want to limit the value size with a length, rather a specification of storage used (e.g. as TINYINT, SMALLINT, INT, BIGINT),
then use a fixed decimal datatype (NUMERIC() for whole numbers, DECIMAL() otherwise).

If you _do_ use VARCHAR() for storage of numbers that you want to be able
to sort on, you would need to have leading zeroes (e.g. '002' instead of
'2'). That's usually adding complexity that you don't need.

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 10/27/2019 11:47 AM, Lyle H. Gray wrote:

Jerry Stuckle <jstucklex@attglobal.net> wrote in news:qp4bqs$q6t$1@jstuckle.eternal-september.org:

On 10/27/2019 6:09 AM, ^Bart wrote:

You use the size of variable you need. if the values in the age
column are really going to exceed the time to the heat death of the
universe, use a bigint

Ok, now I understood, I could use for the age also VARCHAR(3) because
it means I can insert in a row till three numbers (I know VARCHAR is
for string but I could use also to insert numbers!) but the column's
value is "infinity" so I could have a column of millions and millions
of values but these values are till three items!

The mistake was to think to the columns and not to the value of the
row!

Thanks!
^Bart

You do NOT want to store numeric data as strings. For one thing,
sorting will be crap. '2' will be larger than '15'.

Agreed. If you want to limit the value size with a length, rather a specification of storage used (e.g. as TINYINT, SMALLINT, INT, BIGINT),
then use a fixed decimal datatype (NUMERIC() for whole numbers, DECIMAL() otherwise).

If you _do_ use VARCHAR() for storage of numbers that you want to be able
to sort on, you would need to have leading zeroes (e.g. '002' instead of '2'). That's usually adding complexity that you don't need.

Not to mention you'll also have to search for '002' instead of 2.

But there are better ways than using a decimal type - i.e. triggers to
limit the range of values.
--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

Jerry Stuckle <jstucklex@attglobal.net> wrote in news:qp5ij4$9l$1@jstuckle.eternal-september.org:

On 10/27/2019 11:47 AM, Lyle H. Gray wrote:

Jerry Stuckle <jstucklex@attglobal.net> wrote in
news:qp4bqs$q6t$1@jstuckle.eternal-september.org:

You do NOT want to store numeric data as strings. For one thing,
sorting will be crap. '2' will be larger than '15'.

Agreed. If you want to limit the value size with a length, rather a
specification of storage used (e.g. as TINYINT, SMALLINT, INT,
BIGINT), then use a fixed decimal datatype (NUMERIC() for whole
numbers, DECIMAL() otherwise).

If you _do_ use VARCHAR() for storage of numbers that you want to be
able to sort on, you would need to have leading zeroes (e.g. '002'
instead of '2'). That's usually adding complexity that you don't
need.

Not to mention you'll also have to search for '002' instead of 2.

Good point.

But there are better ways than using a decimal type - i.e. triggers to
limit the range of values.

You and I have different definitions of "better". :D

Perhaps a CHECK CONSTRAINT to limit the values, especially if you want to insure that they're positive values (which you would for ID values).
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 10/28/2019 9:17 AM, Lyle H. Gray wrote:

Jerry Stuckle <jstucklex@attglobal.net> wrote in news:qp5ij4$9l$1@jstuckle.eternal-september.org:

On 10/27/2019 11:47 AM, Lyle H. Gray wrote:

Jerry Stuckle <jstucklex@attglobal.net> wrote in
news:qp4bqs$q6t$1@jstuckle.eternal-september.org:

You do NOT want to store numeric data as strings. For one thing,
sorting will be crap. '2' will be larger than '15'.

Agreed. If you want to limit the value size with a length, rather a
specification of storage used (e.g. as TINYINT, SMALLINT, INT,
BIGINT), then use a fixed decimal datatype (NUMERIC() for whole
numbers, DECIMAL() otherwise).

If you _do_ use VARCHAR() for storage of numbers that you want to be
able to sort on, you would need to have leading zeroes (e.g. '002'
instead of '2'). That's usually adding complexity that you don't
need.

Not to mention you'll also have to search for '002' instead of 2.

Good point.

But there are better ways than using a decimal type - i.e. triggers to
limit the range of values.

You and I have different definitions of "better". :D

Perhaps a CHECK CONSTRAINT to limit the values, especially if you want to insure that they're positive values (which you would for ID values).

Yes, for something as simple as this a CHECK CONSTRAINT would work fine.
--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================
--- Synchronet 3.21d-Linux NewsLink 1.2