Book Image

MariaDB Cookbook

By : Daniel Bartholomew
Book Image

MariaDB Cookbook

By: Daniel Bartholomew

Overview of this book

Table of Contents (20 chapters)
MariaDB Cookbook
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

Updating DATETIME and TIMESTAMP columns automatically


If our database has a DATETIME or TIMESTAMP column that we want to be updated whenever the record is updated, there is no need for us to put that logic in our application. MariaDB can take care of it for us.

How to do it...

  1. Launch the mysql command-line client application and connect to our MariaDB server.

  2. Create a test database if it doesn't already exist and switch to it using the following command:

    CREATE DATABASE IF NOT EXISTS test;
    USE test;
    
  3. Create a simple table named dtts using the following commands:

    CREATE TABLE dtts ( 
      id int(11) NOT NULL AUTO_INCREMENT, 
      name varchar(25), 
      dt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), 
      ts timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) 
        ON UPDATE CURRENT_TIMESTAMP(3), 
      PRIMARY KEY (id) 
    );
    
  4. Insert some data into our new table using the INSERT command:

    INSERT INTO dtts (name) VALUES 
      ('Thomass'),('Gordon'),('Howard'),('Ezra'); 
    
  5. Fix the misspelling of Thomas:

    UPDATE dtts...