Mark Hensler — Wed, 27 Aug 2003 06:42:52 +0000

I found the explanation you quoted to be a bit confusing. Especially this part: "first number continues to represent the storage requirements for the value in bytes" I'm not sure how floats are stored, so I can't calculate the number of bytes required.

But after reading the FLOAT(precision) and FLOAT(M,D) descriptions (here: 6.2 Column Types), FLOAT(5,2) sounds right.

m3rajk — Tue, 26 Aug 2003 20:46:58 +0000

and from mysql's online manual....

Quote: The FLOAT type is used to represent approximate numeric datatypes. The SQL-92 standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. The MySQL implementation also supports this optional precision specification. When the keyword FLOAT is used for a column type without a precision specification, MySQL uses four bytes to store the values. A variant syntax is also supported, with two numbers given in parentheses following the FLOAT keyword. With this option, the first number continues to represent the storage requirements for the value in bytes, and the second number specifies the number of digits to be stored and displayed following the decimal point (as with DECIMAL and NUMERIC). When MySQL is asked to store a number for such a column with more decimal digits following the decimal point than specified for the column, the value is rounded to eliminate the extra digits when the value is stored.

so i want float(5,2) .. hmm.. wonder if it'd be better to do the multiplication thing instead... look like that'd be beneficial for storage, but i think i'll go with float since it's easier to use in the actual application if i just pull it out as is.unlesssomone has some comment on this about something i'm missing.

m3rajk — Tue, 26 Aug 2003 20:14:09 +0000

yeah. the php books seem to help but i don't like the mysql one. too many things i can't find. i'm about to go off to the mysql website and see if they describe floats better. i want to keep gmt offset and since it can range from -12 to +12 with one number being somethig like 5.75 (have the exact elsewhere) a float is needed to keep that unless i use a small int and multiple everything by 100, which is something i'm wondering if it'd be beter to do since they are half the size of a float. i'm not sure if i should call it with float(4,2) or float(3,2) since sql will probably look at it as -12.00 to +12.00

Mark Hensler — Tue, 26 Aug 2003 17:03:23 +0000

Oh, and I'd throw out that O'Reilly book.

Mark Hensler — Tue, 26 Aug 2003 17:02:11 +0000

I recommend using varchar(255) over tinytext.

varchar can be index easily, and var-char is short for variable length character field. Meaning a varchar(255) isn't always 255 bytes long. A varchar(255) may store up to 255 bytes of data. The actuall field length is 1 byte plus the length of the data stored. (I believe the extra byte preceeds the data and is read by mysql as an integer representation of the length of the data stored, or an offset for the next field)

A char() field, on the other hand. Is a fixed length field. A char(255) will always be 255 bytes long, irregardless of the length of data being stored in it.

To index off of text or blob fields, you need to specify how many bytes into the field mysql should use for the index. This is because it is very inefficient to index the whole field (especially when we're talking kilobyte, megabyte, or even gigabyte field lengths).

CREATE TABLE forums(
  forum_id tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
  title tinytext NOT NULL,
  blurb tinytext,
  access tinyint unsigned NOT NULL,
  threads smallint,
  last_post_auth varchar(15) NOT NULL,
  last_post_time time default '00:00:00' NOT NULL,
  INDEX title (title(25))
) TYPE=MyISAM;