We should use the following statement to calculate one day ago date time using MySQL:
SELECT NOW() - INTERVAL 1 DAY;
Category Archives: SQL
How to get next auto increment value with query on MySQL
The following select query returns next auto increment value:
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "db_name" AND TABLE_NAME = "table_name"
Example usage of query with MySQL replace
UPDATE your_table SET url = REPLACE(url, 'test/1', 'test/2') WHERE url LIKE '%test/1%'
How to retrieve numeric indexes from enum type values in MySQL
Example usage is like the following:
CREATE TABLE persons(
id INT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
surname CHAR(30) NOT NULL,
gender ENUM('male','female','unisex') NOT NULL,
PRIMARY KEY (id,gender)
);
INSERT INTO persons(name,surname,gender) VALUES('erman','gülhan','male'),('ayşe','dolu','female'),('ali','veli','male');
# this returns gender as string
SELECT name,gender FROM persons;
# this returns gender as numeric index
SELECT name,gender+0 FROM persons;