I have members on my team who are tossing around the idea to make every column in the database a string including numeric columns. I know that sorting becomes an issue. What are the other downsides of making a numeric column a string? Answer The major issue is that users can put broken data into the columns &…
Tag: mysql
How can I make my sql trigger about inappropiate words work
I have a database with different tables in order to store photos, users, reviews, ratings… I wanna validate that no one uses “bad words” (insults) in their photos’ title or description. So I decided to create a table called ‘inappropiatewords’ where all of these bad words w…
Joining table only once in SQL
I’m sure this is very basic, but I’ve hit a brick wall. First post here, long time reader. I’ve got two tables, one is a list of customers, one is a list of their activity by month. For some unknown reason, some of the data in the activity column is duplicated and it’s causing me error…
Why does MySQL not lock rows while searching indexed column
For example, I lock some rows: c2 is not indexed. It means MySQL has to search entire table and if it read uncommitted or read committed isolation levels it places locks on every scanned row and if it doesn’t satisfy the WHERE condition it immediately release the lock. If it is repeatable read those loc…
Variable to replace %s for sql is not getting honored
I have a table with single empty row inserted. Later I want to update column with some value into the row. I am trying to compose a function which will work for all the columns as shown upon execution update_column(‘fieldName’, 10), getting following error. Any help is appreciated. Failed to execu…
How to get first character-set of string in sql?
I want to get 202500 and 37500 without not adding removed characters (in eg: 7, 5). (ie. first string gp in columns).sample-image description here But, I don’t know to get string in front of blank string “”. I have a little exp in sql. pls show me ans. Answer Use SUBSTRING_INDEX instead | Ex…
How to select only entries that didn’t repeat before a specific date?
My issue is that I’m stuck on making a select query where I need to select only items from latest 24 hours, that don’t repeat themselves on entries before those 24 hours. Here is a table: name date Mark 2021-05-27 Jake 2021-05-27 Anthony 2021-05-27 Anthony 2021-05-26 Expected output: name Mark Jak…
Get last record from joined table
I have two tables DOCUMENT and SIGNATURES, like below And I’m try to get last signature name. I have a SQL FIDDLE with database and query, but when run search no record has found. http://sqlfiddle.com/#!9/b98474/3 Here my query Answer A simple method is a correlated subquery: With an index on signatures…
How to select JSON property in SQL statement?
I have field of type json. Example value is: [{“id”: “960287”, “src_ip”: “X.X.X.X”, “filename”: “XXX-20200408092811-0”, “order_id”:…
Creating a trigger that records modifications on table A by inserting values in table B
I’m having problems with the following trigger that records when an insertion with attribute ‘numero’= 4 is made on table A by inserting into table B who made the modification and the number of …