Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 540 of 547
How to remove all non-alphanumeric characters from a string in MySQL?
Non-alphanumeric characters are as follows − @, !, #, &, (), ?, / There is no inbuilt function to remove non-alphanumeric characters from a string in MySQL. Therefore, we create a function which removes all non-alphanumeric characters. The function declaration and definition is as follows. mysql> delimiter // mysql> CREATE FUNCTION RemoveNonAlphaNumeric( s CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC -> BEGIN -> DECLARE var1, length SMALLINT DEFAULT 1; -> DECLARE result CHAR(255) DEFAULT ''; -> DECLARE ch CHAR(1); ...
Read MoreWhy do integers in database row tuple have an 'L' suffix in MySQL?
The ‘L’ suffix concept in MySQL can be related with Python. In Python 2, the long integer literal is suffixed with L or l, but int and long have been binded into int in version 3. Therefore, there is no need for L or l. Adding large numbers in Python Version 3.7 (Python 3), without using any suffix. Here, if we suffix L or l, Python 3 gives an error. However, Python Version 2 suffixed with L or l will not give an error. The following is the output with no error. Hence, Python int is ...
Read MoreWhat is the maximum length of a table name in MySQL?
The maximum length of a table name is 64 characters long according to MySQl version 8.0.12. Check your installed MySQL version. mysql> select version(); The following is the output. +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.03 sec) We can check the maximum length of the table name at the time of creating it. If we give more than 64 characters, then it will not create a table and an error is thrown. Creating a table which has more than 64 characters of table name. mysql> ...
Read MoreWhat is difference between Boolean and tinyint(1) in MySQL?
The basic difference between Boolean and tinyint(1) is only in the naming convention. If we say that we need true or false values then Boolean comes to our mind, instead of tinyint(1). These data types are synonyms. It is up to us which data type we want to use- values can be 1 and 0 or true and false. The following is an example. Creating a table with Boolean data type. mysql> create table BooleanDemo -> ( -> Light Boolean -> ); Query OK, 0 rows affected (0.52 sec) ...
Read MoreWhat is cardinality in MySQL?
In MySQL, the term cardinality refers to the uniqueness of data values that can be put into columns. It is a kind of property which influences the ability to search, cluster and sort data. Cardinality can be of two types which are as follows − Low Cardinality − All values for a column must be same. High Cardinality − All values for a column must be unique. The concept of high cardinality is used if we put a constraint on a column in order to restrict duplicate values. High Cardinality The following is an example of High Cardinality, ...
Read MoreWhich MySQL datatype to used to store an IP address?
We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format. Let us see an example. Creating a table. mysql> create table IPV4AddressDemo -> ( -> `IPV4Address` INT UNSIGNED -> ); Query OK, 0 rows affected (0.52 sec) Inserting IP address into the table, with INET_ATON. mysql> insert into IPV4AddressDemo values(INET_ATON("120.0.0.1")); Query OK, 1 row affected (0.17 sec) To display all records. mysql> select *from IPV4AddressDemo; The following ...
Read MorePass array to MySQL stored routine?
We need to create a stored procedure to display how to pass array to MySQL stored routine. Let us first create a table for our example. Creating a table mysql> create table FindDemo -> ( -> name varchar(100) -> ); Query OK, 0 rows affected (0.46 sec) Inserting some records into the table. mysql> insert into FindDemo values('John'), ('Smith'); Query OK, 2 rows affected (0.13 sec) Records: 2 Duplicates: 0 Warnings: 0 To display all records. mysql> select *from FindDemo; The following is ...
Read MoreHow to handle fragmentation of auto increment ID column in MySQL?
Whenever we renumber, there might be a problem. There is a need to declare a unique ID for a column. In MySQL version 5.6 InnoDB, we can reuse the auto_increment ID by including the ID column in an INSERT statement and we can give any specific value that we want. The situations are as follows − Whenever we delete the ID with the highest number Whenever we start and stop MySQL server Whenever we insert a new record Example of ID auto increment using auto_increment variable. mysql> create table UniqueAutoId -> ( ...
Read MoreMethods for tracking database schema changes in MySQL?
Whenever a table is present in a project with a single database, we can do database schema changes using schema version or migration. It aims to keep track of database schema changes or structural changes. The table creation to keep track of schema changes. mysql> create table SchemaDatabaseMethodDemo -> ( -> `WhenTime` timestamp not null default CURRENT_TIMESTAMP, -> `TheKey` varchar(200) not null, -> `Version` varchar(200), -> primary key(`TheKey`) -> )ENGINE=InnoDB; Query OK, 0 rows affected (0.45 sec) Inserting records into ...
Read MoreHow do I get the current time zone of MySQL?
The following is the syntax to get the current time zone of MySQL. mysql> SELECT @@global.time_zone, @@session.time_zone; The following is the output. +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ 1 row in set (0.00 sec) The above just returns “SYSTEM” because MySQL is set for system time zones. Alternately, we can get the current time zone with the help of now() function. Let us first ...
Read More