Here is a table structure (e.g. test): Field Name Data Type id BIGINT (20) title varchar(25) Description Text A query like: But I would like to order by the field size/length of the field description. The field type will be TEXT or BLOB. Answer The LENGTH function gives the length of string in bytes. If you w…
Tag: mysql
What does the specified number mean in a VARCHAR() clause?
Just to clarify, by specifying something like VARCHAR(45) means it can take up to max 45 characters? I remember I heard from someone a few years ago that the number in the parenthesis doesn’t refer to …
How to search multiple columns in MySQL?
I’m trying to make a search feature that will search multiple columns to find a keyword based match. This query: works only for searching one column, I noticed separating column names with commas results in an error. So is it possible to search multiple columns in mysql? Answer You can use the AND or OR…
Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )
Is there anything like this: TEST DELETE FROM user WHERE somekey = 45; That can return any errors, for example that somekey doesn’t exist, or some constraint violation or anything, and reporting …
How can I reset an MySQL AutoIncrement using a MAX value from another table?
I know this won’t work. I tried it in various forms and failed all times. What is the simplest way to achieve the following result? This is great for automation projects. Answer Use a prepared statement:
Two single-column indexes vs one two-column index in MySQL?
I’m faced with the following and I’m not sure what’s best practice. Consider the following table (which will get large): id PK | giver_id FK | recipient_id FK | date I’m using InnoDB and from what I understand, it creates indices automatically for the two foreign key columns. However, …
MySql conditional order by
I have this table (simplified): CREATE TABLE `my_table` ( `id` INT NOT NULL AUTO_INCREMENT , `item_name` VARCHAR(45) NULL , `price` DECIMAL(10,0) NULL , PRIMARY KEY (`id`) ) I need to select …
Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
How can I get row count values in MySQL as @@ROWCOUNT does in mssql? Answer For SELECTs you can use the FOUND_ROWS construct (documented here): which will return the number of rows in the last SELECT query (or if the first query has a LIMIT clause, it returns the number of rows there would’ve been witho…
Can MySQL convert a stored UTC time to local timezone?
Can MySQL convert a stored UTC time to local time-zoned time directly in a normal select statement? Let’s say you have some data with a timestamp (UTC). CREATE TABLE `SomeDateTable` ( `id` int(11)…
How to verify if two tables have exactly the same data?
Basically, we have one table (original table) and it is backed up into another table (backup table); thus the two tables have exactly the same schema. In the beginning, both tables (original table and backup table) contains exactly the same set of data. After some time for some reason, I need to verify whethe…