Questions tagged [mysql-function]
75 questions
5
votes
1 answer
MySQL: create a function but it already exists
MySQL Server version: 5.1.73 Source distribution.
I can't create a function, it is still exists after I drop it.
Drop the function first:
mysql> drop function if exists `xapps_f20160817`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show…
J.H
- 63
- 1
- 1
- 6
3
votes
1 answer
Formatting MySQL results as currency
What will be the best way to include the result with dollar sign?
I was using something like CONCAT('£', SUM(Deposits)) AS TotalDeposits but it seems not working.
tepmurt
- 41
- 1
- 3
2
votes
1 answer
Generate a sequence of square numbers till 10
How to generate a sequence of SQUARE numbers till 10 in MYSQL? (1^2,2^2, etc)
I was only able to generate a numerical sequence from 1 to 10.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT (n + 1) FROM cte WHERE n < 10
)
SELECT n FROM…
Mouse12328
- 23
- 3
2
votes
1 answer
How to use JSON_ARRAY_APPEND when path does not exist in JSON doc?
Imaging the existing JSON doc:
{
"first": "data",
"second": [1,2,3]
}
When I try to execute:
JSON_ARRAY_APPEND(doc,'$.third',4)
I expect mysql to create the parameter as an empty array and add my element into that array resulting in:
{
…
amaster
- 1,915
- 5
- 25
- 51
2
votes
1 answer
Error in MySQL function with parameters
I have MySQL function with 2 parameters namely user_id and post_id
Here's my function:
CREATE FUNCTION isliked(pid INT, uid INT)
RETURN TABLE
AS
RETURN (EXISTS (SELECT 1 FROM likedata ld WHERE post_id = pid AND user_id = uid
)) as…
Purge Project
- 39
- 5
1
vote
4 answers
MySQL: inserting datetime.date(2023,6,22) into a table
I have an sql file containing, say,
INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 2, 22));
How do I create a table that will accept this? More explicitly, in
CREATE TABLE mytable(ID INT NOT NULL, Date ???)) ENGINE=INNODB;
what…
Cecil Scott
- 11
- 1
1
vote
1 answer
Can't create a functional index on top of my custom function
I'm playing with functional index in MySQL 8, and i'm using the *employees* database.
So I created this function:
DELIMITER $$
CREATE FUNCTION salary_range2(salary DECIMAL(10,2))
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN…
luca84
- 13
- 3
1
vote
1 answer
Mysql function to genarate custom ids
I need some help in creating a MySQL function
This function generates a user id for my user, Which generates 5 digits unique id starting from A0001, A0002, B0001, C0001, and so on but the problem is it reaches F9999 as per my function the following…
Md Hasibur Rahaman
- 1,041
- 3
- 11
- 34
1
vote
1 answer
Create and call MYSQL Function to find Fibonacci numbers till n numbers
My approach
DELIMITER $$
CREATE FUNCTION fibonacci(num INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE fib1 INT DEFAULT 0;
DECLARE fib2 INT DEFAULT 1;
DECLARE fib3 INT DEFAULT 0;
DECLARE str VARCHAR(255) DEFAULT '01';
IF num =…
Albert Einstein
- 59
- 1
- 7
1
vote
2 answers
MySQL function execution time increases everytime I SELECT it
Bit of a strange one. I have a function that runs perfectly fine at first, say 15ms execution and 10ms fetching when selecting the returned value plus some other columns. But if keep refreshing the same query, over and over, the execution of the…
Damian
- 21
- 1
- 5
1
vote
1 answer
NTILE function in Mysql get to well distribution over column
Hi this query i am trying to run in MySQL to get well distribution over my ID but looks like some issue in syntax .
select min(ID), max(ID),count(*), nt from ( select ID, ntile(16) over (order by ID) nt from table) group by nt order by nt;
This is…
Sudarshan kumar
- 1,503
- 4
- 36
- 83
1
vote
1 answer
How to avoid error on MYSQL CONVERT() method, when passing a 0 second string and subtract from that value?
I am using MySQL 8 and have a problem with this type of query:
INSERT INTO review (name, create_date) VALUES('name', CONVERT(timestamp, DATETIME) - 1)
I have not had this error when using this expression in a where clause.
When the value for the…
Paul Werner
- 87
- 1
- 8
1
vote
1 answer
My LAG function not working as expected MySQL 8.x
My table looks like this:
metro_region, value, date
with multiple values (one for each date) across the month of November. There are about 100 metro regions.
I want my report to have the following data:
Metro_region Today Yesterday 2daysAgo …
firedude144
- 43
- 4
1
vote
0 answers
MySQL 8 get PROCEDURE / FUNCTION full definition
I need to get the user defined PROCEDURE / FUNCTION full definition in MySQL 8.
MySQL 5.6 contain mysql.proc table there we can get the function full definition but MySQL 8 does not contain the proc table.
But MySQL 5.6 and 8 also have…
Nasik Ahd
- 778
- 1
- 9
- 22
1
vote
1 answer
How to write a MySQL function to extract JSONAttribute's value, based on name?
I have a requirement that I need to write a mysql function which takes 'JSONText', 'color' as parameters and output based on the 'color'
[
{
"color":"red",
"points":4
},
{
"color": "Electric Blue",
"points": 5
}
]
So, my…
Srinivas Lakshman
- 469
- 2
- 4
- 21