Mysql

MySql ENUM vs SET

Ananda Raj PandeyLeave a comment

MySql ENUM  vs SET

From MySql Documentation:

ENUM and SET columns provide an efficient way to define columns that can contain only a given set of values.

With strict mode enabled, the definition of a ENUM or SET column acts as a constraint on values entered into the column. An error occurs for values that do not satisfy these conditions:

  • An ENUM value must be one of those listed in the column definition, or the internal numeric equivalent thereof. The value cannot be the error value (that is, 0 or the empty string). For a column defined as ENUM('a','b','c'), values such as '', 'd', or 'ax' are invalid and are rejected.
  • A SET value must be the empty string or a value consisting only of the values listed in the column definition separated by commas. For a column defined as SET('a','b','c'), values such as 'd' or 'a,b,c,d' are invalid and are rejected.

Errors for invalid values can be suppressed in strict mode if you use INSERT IGNORE or UPDATE IGNORE. In this case, a warning is generated rather than an error. For ENUM, the value is inserted as the error member (0). For SET, the value is inserted as given except that any invalid substrings are deleted. For example, 'a,x,b,y' results in a value of 'a,b'.

what is MySql signed / unsigned option?

Ananda Raj PandeyLeave a comment

images

When to use signed / unsigned option in MySql.

Mysql Says: 

All integer types can have an optional (nonstandard) attribute UNSIGNED. Unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column’s range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.

When to use UNSIGNED data type?

If your field will never stores negative values, then its best to use UNSIGNED, that will give you faster index and more range.

When to use  SIGNED data type?

If you need to store negative then use signed, but remember your number range is half now.

 

Note: Common mistake people do is to store primary number ( auto -increment ) as as signed. Which mean you are already reducing your number range even you will never use negative number as your primary key.

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer typesTINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.

Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615