-4
The attribute I want to store refers to a class, the code of this class must contain integers and letters, and it is a primary key attribute. Example of value I want to store: SI3P39
3 answers
2
This seems to be the case of using a CHAR because is a description and with fixed size. I may be wrong as to whether it is of fixed size, in this case it would change to a VARCHAR, but doubt even this type is not ideal candidate for primary key, although it works.
Even if it was just numbers, if it was still a description it would probably still be CHAR, numerical types exist to store quantities, to make calculations. Note that there are no integers in this code there are texts with digits, integer implies that there will be mathematics in this.
Just think if you’re really going to use this as a primary key, I’ve seen a lot of cases that seemed like a good idea to use a natural key, but over time it was discovered that she would need to change and then it was a childbirth to tidy up the entire database and did not even solve about external references. A replacement key solves this. Think hard about the concrete problem. Nor will I get into the merit that this code may not even be ideal for the company process, also I’ve seen a lot of error with codes within this standard, but this is not the problem of technology.
For question asked it makes no sense to know a list of all types.
1
If this class code has a specific size, that is, it will always have 6 characters, you can use the type CHAR, if it is of variable size, that is, it can have 4, 6, 8 or N characters, then use VARCHAR
In both cases should not define the key primacy as AUTO_INCREMENT, then when adding a line you must pass the primary key explicitly
See the difference between CHAR and VARCHAR
Another option is to use an auto incremented integer, as is done in most cases, and set the class field to UNIQUE, so it will not repeat. If you try to generate an error
-1
Key considerations on data types.
Data types in strings
Strings are strings of characters. In Mysql, a string can have any content, from plain text to binary data - such as images and files. Strings can be compared and searched.
CHAR - a fixed and non-binary string (string); - Columns of type CHAR(n) have pre-set fixed size where n' will be equal to the total storage bytes that the column will occupy.
SWEEP - a variable and non-binary size string; - Columns of type VARCHAR(n) occupy 1 byte per character reported in the record.
BINARY - a fixed size binary string;
VARBINARY - a binary string of variable size; - VARBINARY is connected to 255 bytes in Mysql 5.0.2 and below, to 65kB in 5.0.3 and above.
BLOB - a small Binary Large Object (BLOB); - A binary large object column with a maximum length of 65535 (2 16 - 1) characters.
TINYBLOB - a very small BLOB; - A binary large object column with a maximum length of 255 (2 8 - 1) characters.
MEDIUMBLOB - a medium-sized BLOB; - A large binary object column with a maximum length of 16777215 (2 24 - 1) characters.
LONGBLOB - a large BLOB; - A binary large object column with a maximum size of 4294967295 (2 32 - 1) characters.
TINYTEXT - a non-binary string of very small size; - up to 255 bytes, 1 byte overload
TEXT - a small, non-binary string; - up to 64 Kb, 2 bytes overload
MEDIUMTEXT - a string of common and non-binary size; - up to 16 Mb, 3 byte overload
LONGTEXT - a large non-binary string; - up to 4 Gb, 4 bytes overload
ENUM - according to the Mysql manual, it is a string, with a value that needs to be selected from a predefined list when creating the table; - An ENUM value must be one of those listed in the definition of the column, or its internal numerical equivalent. The value cannot be the value of the error (i.e., 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.
SET - is an object that can have zero or more values - each of which needs to be chosen from a list of predetermined values when creating the table. - A SET value must be the empty string or a value consisting only of the values listed in the definition of the comma-separated column. For a column defined as SET ('a', 'b', 'c'), values such as’d' or 'a, b, c, d' are invalid and are rejected.
Numeric data types in Mysql
Mysql has all possible numeric types, which includes exact, approximate, integer, fixed point, floating point etc. The list below shows a summary of possible data types in Mysql:
TINYINT — número inteiro muito pequeno (tiny);
SMALLINT — número inteiro pequeno;
MEDIUMINT — número inteiro de tamanho médio;
INT — número inteiro de tamanho comum;
BIGINT — número inteiro de tamanho grande;
DECIMAL — número decimal, de ponto fixo;
FLOAT — número de ponto flutuante de precisão simples (32 bits);
DOUBLE — número de ponto flutuante de precisão dupla (64 bits);
BIT — um campo de um bit.
Here follows a link to byte string calculation: https://lingojam.com/ByteCounter
Types of dates you can check out more on the official website: https://dev.mysql.com/doc/refman/8.0/en/data-types.html
Browser other questions tagged mysql database typing
You are not signed in. Login or sign up in order to post.
You can keep the id attribute as key first, and add a column of type
charwith the attributeunique, (if it is a single value) and indexes it.– Bulfaitelo
Is your question not related to the code you will use? Because asking to list everything that exists is considered a broad question. Here we ask specific questions, and it seemed that what you wanted was about your specific code problem. Knowing all types that exist does not help your specific problem.
– Maniero