Khóa học miễn phí MySQL – FLOAT nhận dự án làm có lương

MySQL – FLOAT

Table of content


The FLOAT data type is a part of the numeric data type. Numeric data types are used to store numbers, and they can be categorized into various subtypes based on their characteristics, such as storage size and precision.

The MySQL FLOAT Data Type

The MySQL FLOAT datatype is a floating-point number type that stores approximate numeric values. It stores approximate numeric values in 4 bytes and represents single-precision values.

FLOAT is suitable for a wide range of numeric values but stores them in an approximate manner due to the IEEE 754 standard limitations.

FLOAT data type can represent both signed and unsigned attributes of a data value in versions prior to MySQL 8.0.17, but the unsigned FLOAT is deprecated in MySQL 8.0.17 and later versions.

Syntax

Following is the basic syntax to set the datatype of a field as FLOAT −

CREATE TABLE (column_name FLOAT, ...);

Example

In this example, let us create a new database table named ”datatype_demo” using CREATE TABLE statement with columns representing FLOAT values −

CREATE TABLE datatype_demo(
   ID INT, 
   NAME VARCHAR(50), 
   HEIGHT FLOAT, 
   WEIGHT FLOAT
);

Following is the output obtained −

Query OK, 0 rows affected (0.03 sec)

Verification

Once the table is created, we can verify the data types of the ”HEIGHT” and ”WEIGHT” fields by retrieving the table”s definition as shown below −

DESC datatype_demo;

The result of the DESC command will show that the ”HEIGHT” and ”WEIGHT” fields have the FLOAT data type −

Field Type Null Key Default Extra
ID int YES NULL
NAME varchar(50) YES NULL
HEIGHT float YES NULL
WEIGHT float YES NULL

To verify further, let us insert some values into the table using the following INSERT statement −

INSERT INTO datatype_demo VALUES
(1, ''John'', 171.3, 65.7),
(2, ''Rob'', 45, 75),
(3, ''Salman'', 12.74839, 54.262),
(4, ''Arush'', NULL, NULL),
(5, ''James'', ''h'', ''w'');

Following is the output obtained −

ERROR 1265 (01000): Data truncated for column ''HEIGHT'' at row 1

As expected, the FLOAT fields accept single precision floating-point numbers without any issues. However, when attempting to insert non-numeric values into these fields, such as ”h” and ”w,” MySQL raises an error, indicating data truncation.

Finally, to view the data that has been inserted into the table, we can use the SELECT statement as shown below −

SELECT * FROM datatype_demo;

The resultant table is as follows −

ID NAME HEIGHT WEIGHT
1 John 171.3 65.7
2 Rob 45 75
3 Salman 12.7484 54.262
4 Arush NULL NULL

Other Representations of MySQL FLOAT

MySQL has a provision to specify the range of precision (not the exponent) for the FLOAT datatype in the form of bits. These bits are specified within the parenthesis following the keyword FLOAT, i.e. FLOAT(p).

However, this precision value is only used to determine the storage size and only holds up to 7 decimal places, with the range from 0 to 23 bits. If the precision bit exceeds 23, the data type becomes DOUBLE.

Example

First, we will drop the existing ”datatype_demo” table −

DROP TABLE datatype_demo;

The output obtained is as follows −

Query OK, 0 rows affected (0.01 sec)

Then, we will create a new table ”datatype_demo” specifying a precision of 20 bits for the ”HEIGHT” column −

CREATE TABLE datatype_demo(
   ID INT,
   NAME VARCHAR(50),
   HEIGHT FLOAT(20)
);

Following is the output of the above code −

Query OK, 0 rows affected (0.02 sec)

Verification

Even though we specified a precision of 20 bits, the ”HEIGHT” column will still store float values within the single-precision range, holding up to 7 decimal places. To verify the table”s definition, we can use the DESC command as shown below −

DESC datatype_demo;

The table produced is as follows −

Field Type Null Key Default Extra
ID int YES NULL
NAME varchar(50) YES NULL
HEIGHT float YES NULL

If the precision bit exceeds 23, the datatype becomes DOUBLE. Look at the query below −

CREATE TABLE datatype_demo1(
   ID INT,
   NAME VARCHAR(50),
   HEIGHT FLOAT(30)
);

we get the following output −

Query OK, 0 rows affected (0.02 sec)

Again, we can verify the table”s definition using the DESC command −

DESC datatype_demo1;

Following is the table obtained −

Field Type Null Key Default Extra
ID int YES NULL
NAME varchar(50) YES NULL
HEIGHT double YES NULL

Float Datatype Using a Client Program

We can also create column of the Float datatype using the client program.

Syntax

To create a column of Float datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −

$sql = ''CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))
$mysqli->query($sql);

To create a column of Float datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −

sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
con.query(sql);

To create a column of Float datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
statement.execute(sql);

To create a column of Float datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −

sql = ''CREATE TABLE datatype_demo(ID INT, NAME VARCHAR(50), HEIGHT FLOAT, WEIGHT FLOAT)'''' 
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf(''Connected successfully.
''); //creating a table temp where we are inserting the celsius and Fahrenheit values in float $sql = ''CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id)) $result = $mysqli->query($sql); if ($result) { printf("Table created successfully...!n"); } // insert data into created table $q = " INSERT INTO temp(Celsius, Fahrenheit) VALUES ( 36.2, 97.16), ( 35.8, 96.44), ( 37.32, 99.17), ( 35.89, 96.602);"; if ($res = $mysqli->query($q)) { printf("Data inserted successfully...!n"); } //now display the table records $s = "SELECT * FROM temp"; if ($r = $mysqli->query($s)) { printf("Table Records: n"); while ($row = $r->fetch_assoc()) { printf(" ID: %d, Celsius: %f, Fahrenheit: %f", $row["Id"], $row["Celsius"], $row["Fahrenheit"]); printf("n"); } } else { printf(''Failed''); } $mysqli->close();

Output

The output obtained is as follows −

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Celsius: 36.200000, Fahrenheit: 97.160000
 ID: 2, Celsius: 35.800000, Fahrenheit: 96.440000
 ID: 3, Celsius: 37.320000, Fahrenheit: 99.170000
 ID: 4, Celsius: 35.890000, Fahrenheit: 96.602000            
var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a student table, that accepts one column of float type.
  sql =
    "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
  con.query(sql);

  //insert data into created table
  sql =
    "INSERT INTO temp(Celsius, Fahrenheit) VALUES ( 36.2, 97.16), ( 35.8, 96.44), ( 37.32, 99.17), ( 35.89, 96.602)";
  con.query(sql);
  //select datatypes of salary
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''temp'' AND COLUMN_NAME = ''Celsius''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});       

Output

The output produced is as follows −

[ { DATA_TYPE: ''float'' } ]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Float {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Float data types...!;
         String sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
         statement.execute(sql);
         System.out.println("column of a Float type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE temp");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}    

Output

The output obtained is as shown below −

Connected successfully...!
column of a Float type created successfully...!
Id int
Celsius float
Fahrenheit float
import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with float column
sql = ''''''
CREATE TABLE datatype_demo(ID INT, 
NAME VARCHAR(50), 
HEIGHT FLOAT,
WEIGHT FLOAT
)
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (1, ''John'', 171.3, 65.7),
    (2, ''Rob'', 45, 75),
    (3, ''Salman'', 12.74839, 54.262),
    (4, ''Arush'', None, None),
]
# Insert data into the created table
insert_query = "INSERT INTO datatype_demo (ID, NAME, HEIGHT, WEIGHT) VALUES (%s, %s, %s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM datatype_demo"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()    

Output

Following is the output of the above code −

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''John'', 171.3, 65.7)
(2, ''Rob'', 45.0, 75.0)
(3, ''Salman'', 12.7484, 54.262)
(4, ''Arush'', None, None)

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc