Sql datatypes: Difference between revisions

From wikinotes
(Created page with "{{ TODO | is this all SQL? or is this MySQL specific? }} {| class="wikitable" !colspan=2| Integers |- | <code>UNIQUE</code> || I need to lookup how this works....")
 
No edit summary
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{ TODO |
{{ NOTE |
is this all SQL? or is this MySQL specific? }}
It is difficult to confirm if these are all officially part of the SQL spec without purchasing it.<br>
Always consult your database docs.
}}


{| class="wikitable"
= Text =
!colspan=2| Integers
<blockquote>
|-
<syntaxhighlight lang="SQL">
| <code>UNIQUE</code>               || I need to lookup how this works. IMPORTANT
CHAR(10)     /* (N-bytes) string. If stores shorter string, remaining space filled with blank characters. */
|-
VARCHAR(10)  /* (N-bytes) string. Can store shorter strings */
| <code>BOOL</code>                  || (alias TRUE/FALSE to TINYINT)
</syntaxhighlight>
|-
</blockquote><!-- Text -->
| <code>TINYINT</code>              || int (-128 to 127)
 
|-
= Blobs =
| <code>SMALLINT</code>              || int (-32768 to 32767)
<blockquote>
|-
<syntaxhighlight lang="SQL">
| <code>MEDIUMINT</code>             || int (-8388608 to 8388607)
BLOB        /* (1-byte) binary blob */
|-
BLOB(10)    /* (N-bytes) binary blob */
| <code>INT</code>                  || int (-2147483648 to 2147483647)
</syntaxhighlight>
|-
</blockquote><!-- Binary Blobs -->
| <code>BIGINT</code>                || int (-9223372036854775808 to 9223372036854775807)
 
|-
= Numbers =
!colspan=2| Float & Double
<blockquote>
|-
<syntaxhighlight lang="SQL">
| <code>FLOAT</code>                 || float (a very big number in scientific notation)
BOOLEAN          /* true/false, TRUE/FALSE                                                                  */
|-
 
| <code>DOUBLE</code>               || double (an even bigger number in scientific notation)
SMALLINT          /* (2-bytes, signed)                               -32_768 ... 32_767                    */
|-
 
!colspan=2| Dates
INT              /* (4-bytes, signed)                         -2_147_483_648 ... 2_147_483_647              */
|-
 
| <code>DATE</code>                 || 2014-01-01
BIGINT            /* (8-bytes, signed)             -9_223_372_036_854_775_808 ... -9_223_372_036_854_775_807 */
|-
 
| <code>DATETIME</code>              || 2014-01-01 12:30:00
DECIMAL          /* (10-digits, 0-decimal-places)             -1_000_000_000 ... 1_000_000_000              */
|-
DECIMAL(10, 2)   /* (10-digits, 2-decimal-places)          -1_000_000_000.00 ... 1_000_000_000.00          */
| <code>TIMESTAMP</code>            || 1970-01-01 00:00:01 (seconds since UNIX EPOCH)
 
|-
FLOAT(10)         /*                                                                                        */
!colspan=2| Strings
</syntaxhighlight>
|-
</blockquote><!-- Numbers -->
| <code>CHAR(4)</code>              || store limited number of characters. byte size is equivalent to number of characters (maxLength 254)
|-
| <code>VARCHAR(4)</code>            || store limited number of characters. byte size only as large as needs to be (note that 1-2 bytes are used to store byte size) (maxLength 254)
|-
| <code>BINARY(4)</code>            || store limited number of binary characters. byte size is equivalent to number of characters (maxLength 254)
|-
| <code>VARBINARY(4)</code>         || store limited number of binary characters. Binary version of VARCHAR. (maxLength 254)
|-
| <code>TINYBLOB/TINYTEXT</code>    || Binary/String (maxLength 256 chars)
|-
| <code>BLOB/TEXT</code>            || Binary/String (maxLength 65536 chars)
|-
| <code>MEDIUMBLOB/MEDIUMTEXT</code> || Binary/String (maxLength 16777216 chars)
|-
| <code>LONGBLOB/LONGTEXT</code>     || Binary/String (maxLength 4294967296 chars)
|}

Latest revision as of 19:07, 19 September 2021

NOTE:

It is difficult to confirm if these are all officially part of the SQL spec without purchasing it.
Always consult your database docs.

Text

CHAR(10)     /* (N-bytes) string. If stores shorter string, remaining space filled with blank characters. */
VARCHAR(10)  /* (N-bytes) string. Can store shorter strings */

Blobs

BLOB         /* (1-byte)  binary blob */
BLOB(10)     /* (N-bytes) binary blob */

Numbers

BOOLEAN           /* true/false, TRUE/FALSE                                                                  */

SMALLINT          /* (2-bytes, signed)                                -32_768 ... 32_767                     */

INT               /* (4-bytes, signed)                         -2_147_483_648 ... 2_147_483_647              */

BIGINT            /* (8-bytes, signed)             -9_223_372_036_854_775_808 ... -9_223_372_036_854_775_807 */

DECIMAL           /* (10-digits, 0-decimal-places)             -1_000_000_000 ... 1_000_000_000              */
DECIMAL(10, 2)    /* (10-digits, 2-decimal-places)          -1_000_000_000.00 ... 1_000_000_000.00           */

FLOAT(10)         /*                                                                                         */
Retrieved from "http://willpittman.net:8080/index.php?title=Sql_datatypes&oldid=27635"