Cara menggunakan mysql decimal null

Question

Summary: in this tutorial, we will introduce you to the MySQL DECIMAL data type and how to use it effectively in your database table.

Table of Contents Show

Introduction to MySQL DECIMAL data type
MySQL DECIMAL storage
MySQL DECIMAL data type and monetary data
MySQL DECIMAL data type example
Apa itu NULL di MySQL?
Apa itu null SQL?
Apa yang dimaksud dengan nilai null?
Apa fungsi dari NOT NULL?

Introduction to MySQL DECIMAL data type

The MySQL DECIMAL data type is used to store exact numeric values in the database. We often use the DECIMAL data type for columns that preserve exact precision e.g., money data in accounting systems.

To define a column whose data type is DECIMAL you use the following syntax:

column_name  DECIMAL(P,D);
Code language: SQL (Structured Query Language) (sql)

In the syntax above:

P is the precision that represents the number of significant digits. The range of P is 1 to 65.
D is the scale that that represents the number of digits after the decimal point. The range of D is 0 and 30. MySQL requires that D is less than or equal to (<=) P.

The

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

2 means that the column can store up to P digits with D decimals. The actual range of the decimal column depends on the precision and scale.

Besides the

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

3 keyword, you can also use

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

4,

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

5, or

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

6 because they are synonyms for

amount DECIMAL(6,2);



Code language: SQL (Structured Query Language) (sql)

3.

Like the INT data type, the

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

3 type also has

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

9 and

column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)

0 attributes. If we use the

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

9 attribute, the column with

column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)

2 will not accept negative values.

In case we use

column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)

0, MySQL will pad the display value by 0 up to display width specified by the column definition. In addition, if we use

column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)

4 for the

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

3 column, MySQL will add the

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

9 attribute to the column automatically.

The following example defines amount column with

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

3 data type.

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

In this example, the amount column can store 6 digits with 2 decimal places; therefore, the range of the amount column is from 9999.99 to -9999.99.

MySQL allows us to use the following syntax:

column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)

This is equivalent to:

column_name DECIMAL(P,0);
Code language: SQL (Structured Query Language) (sql)

In this case, the column contains no fractional part or decimal point.

In addition, we can even use the following syntax.

column_name DECIMAL;
Code language: SQL (Structured Query Language) (sql)

The default value of P is 10 in this case.

MySQL DECIMAL storage

MySQL assigns the storage for integer and fractional parts separately. MySQL uses binary format to store the

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

3 values. It packs 9 digits into 4 bytes.

For each part, it takes 4 bytes to store each multiple of 9 digits. The storage required for leftover digits is illustrated in the following table:

Leftover DigitsBytes001–213–425–637–94

For example,

column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)

9 has 9 digits for the fractional part and 19-9 = 10 digits for integer part. The fractional part requires 4 bytes. The integer part requires 4 bytes for the first 9 digits, for 1 leftover digit, it requires 1 more byte. In total, the

column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)

9 column requires 9 bytes.

MySQL DECIMAL data type and monetary data

We often use the

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

3 data type for monetary data such as prices, salary, account balances, etc. If you design a database that handle the monetary data, the following syntax should be OK.

amount DECIMAL(19,2);
Code language: SQL (Structured Query Language) (sql)

However, if you want to comply with Generally Accepted Accounting Principles (GAAP) rules, the monetary column must have at least 4 decimal places to make sure that the rounding value does not exceed $0.01. In this case, you should define the column with 4 decimal places as follows:

amount DECIMAL(19,4);
Code language: SQL (Structured Query Language) (sql)

MySQL DECIMAL data type example

First, create a new table named

column_name DECIMAL(P,0);



Code language: SQL (Structured Query Language) (sql)

2with three columns: id, description, and cost.

CREATE TABLE materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255),
    cost DECIMAL(19 , 4 ) NOT NULL
);
Code language: PHP (php)

Second, insert data into the

column_name DECIMAL(P,0);
Code language: SQL (Structured Query Language) (sql)

2 table.

INSERT INTO materials(description,cost)
VALUES('Bicycle', 500.34),('Seat',10.23),('Break',5.21);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the

column_name DECIMAL(P,0);
Code language: SQL (Structured Query Language) (sql)

2 table.

SELECT 
    *
FROM
    materials;
Code language: SQL (Structured Query Language) (sql)

Fourth, change the cost column to include the

column_name DECIMAL(P);
Code language: SQL (Structured Query Language) (sql)

0 attribute.

amount DECIMAL(6,2);
Code language: SQL (Structured Query Language) (sql)

0

Fifth, query the materials table again.

SELECT 
    *
FROM
    materials;
Code language: SQL (Structured Query Language) (sql)

As you see, we have many zeros padded in the output values.

In this tutorial, we have shown gave you detailed information on MySQL DECIMAL data type and shown you how to apply it to the columns that store exact numeric data such as financial data.

Apa itu NULL di MySQL?

NULL adalah suatu nilai pada suatu kolom yang berarti tidak mempunyai nilai. NULL tidak sama dengan 0. Sebab angka 0 mempunyai nilai yaitu bernilai 0. NULL juga tidak sama dengan text kosong. NULL adalah suatu nilai yang tidak bernilai sama sekali.

Apa itu null SQL?

Nilai null dalam database relasional digunakan ketika nilai dalam kolom tidak diketahui atau hilang. Null bukanlah string kosong (untuk jenis data karakter atau datetime) atau nilai nol (untuk jenis data numerik).

Apa yang dimaksud dengan nilai null?

Nilai null mengindikasikan bahwa varian tersebut tidak berisi data yang valid. Null tidak sama dengan Kosong, yang mengindikasikan bahwa variabel belum diinisialisasi. Juga tidak sama seperti string panjang nol (""), yang terkadang disebut sebagai string kosong.

Apa fungsi dari NOT NULL?

Atribut NOT NULL dapat digunakan pada hampir semua tipe data, Fungsinya untuk memastikan bahwa nilai pada kolom tersebut tidak boleh kosong. Jika kita menginput data, namun tidak memberikan nilai untuk kolom tersebut, akan menghasilkan error pada MySQL.

kode mysql