[MySQL] mysql에서 사용 가능한 데이터 타입

Certainly! Let's combine the explanations and examples for Enumerated and Set Types, as well as Spatial Data Types, with the previous answer:

1. INT (Integer):

   • Description: A standard integer data type that stores whole numbers without decimal places.

   • Example:

     CREATE TABLE example_int (
         user_id INT,
         age INT
     );

2. BIGINT:

   • Description: Similar to INT but with a larger range, suitable for storing extremely large whole numbers.

   • Example:

     CREATE TABLE example_bigint (
         transaction_id BIGINT,
         account_balance BIGINT
     );

3. FLOAT:

   • Description: A floating-point number with single-precision, suitable for decimals but with a limited precision.

   • Example:

     CREATE TABLE example_float (
         temperature FLOAT,
         weight FLOAT
     );

4. DOUBLE:

   • Description: Similar to FLOAT but with double-precision, providing higher precision for decimal values.

   • Example:

     CREATE TABLE example_double (
         latitude DOUBLE,
         longitude DOUBLE
     );

5. VARCHAR (Variable-Length Character):

   • Description: A variable-length string type that can store alphanumeric characters up to a specified length.

   • Example:

     CREATE TABLE example_varchar (
         first_name VARCHAR(50),
         last_name VARCHAR(50)
     );

6. CHAR (Fixed-Length Character):

   • Description: A fixed-length string type that pads shorter strings with spaces to match the defined length.

   • Example:

     CREATE TABLE example_char (
         country_code CHAR(3),
         airline_code CHAR(2)
     );

7. TEXT:

   • Description: A data type for storing large amounts of text data, suitable for longer strings or documents.

   • Example:

     CREATE TABLE example_text (
         article TEXT,
         description TEXT
     );

8. DATE:

   • Description: Stores a date in the format 'YYYY-MM-DD'.

   • Example:

     CREATE TABLE example_date (
         event_date DATE,
         birth_date DATE
     );

9. TIME:

   • Description: Represents a time of day in the format 'HH:MM:SS'.

   • Example:

     CREATE TABLE example_time (
         start_time TIME,
         end_time TIME
     );

10. DATETIME:

    • Description: Combines date and time, representing a specific point in time with higher precision.

    • Example:

      CREATE TABLE example_datetime (
          log_timestamp DATETIME,
          event_datetime DATETIME
      );

11. TIMESTAMP:

    • Description: Similar to DATETIME but with automatic updating to the current timestamp when a record is inserted or updated.

    • Example:

      CREATE TABLE example_timestamp (
          record_created TIMESTAMP,
          last_updated TIMESTAMP
      );

12. ENUM (Enumerated Type):

    • Description: ENUM is a data type that allows you to define a list of permissible values for a column. It restricts the column to only one of the predefined set values.

    • Example:

      CREATE TABLE example_enum (
          status ENUM('active', 'inactive', 'pending')
      );

      In this example, the example_enum table has a column status that can only take values 'active', 'inactive', or 'pending'.

13. SET Type:

    • Description: Similar to ENUM, SET is a data type that allows you to define a list of permissible values for a column. However, SET columns can have multiple values selected from the predefined set.

    • Example:

      CREATE TABLE example_set (
          features SET('wifi', 'bluetooth', 'gps', 'camera')
      );

      Here, the example_set table has a column features that can have multiple values selected from the set ('wifi', 'bluetooth', 'gps', 'camera').

14. Spatial Data Types:

    • Description: Spatial data types in MySQL are used for storing and querying geometric or geographic data. Common types include POINT, LINESTRING, POLYGON, and GEOMETRY.

    • Example with POINT:

      CREATE TABLE example_spatial (
          location POINT
      );

      In this example, the example_spatial table has a column location using the POINT data type to store specific spatial coordinates.

    • Example with GEOMETRY:

      CREATE TABLE example_geometry (
          shape GEOMETRY
      );

      Here, the example_geometry table has a column shape using the GEOMETRY data type, which can store various types of spatial data like points, lines, or polygons.