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
MySQL Articles
Page 346 of 355
How to escape apostrophe (') in MySQL?
We can escape apostrophe (‘) in MySQL in the following two ways − We can use backslash. We can use single quotes twice (double quoted) Using backslash Let us first create a table. mysql> create table SingleQuotesDemo - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (1.16 sec) Following direct usage does not give the desired result for name “John’s”. mysql> insert into SingleQuotesDemo values(1, 'John's'); '> Let us now use backslash. ...
Read MoreIs it possible to have a function-based index in MySQL?
Function-based index wasn’t possible in MySQL versions below 5.6. Firstly, to create function-based index in MySQL, we will create a table. mysql> create table FunctionIndexDemo - > ( - > FirstName varchar(100) - > ); Query OK, 0 rows affected (0.70 sec) Let us see the syntax to create a function based index. create index index_name on yourTableName (column_name(IntegerSize)); Here is the query. mysql> create index indFirstName on FunctionIndexDemo (FirstName(6)); Query OK, 0 rows affected (0.56 sec) Records: 0 Duplicates: 0 Warnings: 0 ...
Read MoreHow to create a MySQL table with MyISAM engine table?
To create a MySQL table with MyISAM engine, we can use ENGINE command. Let us first create a table using CREATE command. mysql> create table StudentRecordWithMyISAM -> ( -> Id int, -> StudentName varchar(100), -> StudentAge int -> )ENGINE=MyISAM; Query OK, 0 rows affected (0.26 sec) Above, we have set the ENGINE as “MyISAM”. To check how many columns are present in the table, use DESC command. mysql> DESC StudentRecordWithMyISAM; The following is the output. +-------------+--------------+------+-----+---------+-------+ | Field ...
Read MoreHow to convert MyISAM to InnoDB storage engine in MySQL?
To convert the MyISAM Engine to InnoDB, we can use the ALTER command. Let us now create a table with the help of engine MyISAM. mysql> create table MyISAMToInnoDBDemo -> ( -> id int, -> Name varchar(100) -> )ENGINE=MyISAM; Query OK, 0 rows affected (0.19 sec) To check if the table is created with engine MyISAM or not. mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'business' and ENGINE = 'MyISAM'; The following is the output that displays the table created with MyISAM ...
Read MoreHow to debug Lock wait timeout exceeded on MySQL?
The debug Lock wait timeout situation occurs because of some threads. If one thread is holding on to some records for a very long time, it means the thread has exceeded time. To see all the details, implement the following query − mysql> SHOW ENGINE INNODB STATUS; The following is the output. +--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Type | Name | Status ...
Read MoreWhy doesn't MySQL support millisecond / microsecond precision?
The millisecond/ microsecond precision wasn’t supported in previous versions like 5.6.4. But now MySQL supports millisecond/ microsecond precision with timestamp, datetime, and time. The official statement. “MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision”. You can check the MySQL version on your system using the version() method. mysql> SELECT version(); The following is the output. +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.01 sec) Let us now see the syntax to check the date difference. mysql> SELECT DATEDIFF(now(), ...
Read MoreHow do I kill all the processes in MySQL “show processlist”?
We can kill the processes with the help of the ‘kill’ command. However, you need to kill those processes one by one, since MySQL does not have any massive kill command. To check how many processes exist, use ‘show processlist’ mysql> show processlist; The following is the output. +----+-----------------+-----------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State ...
Read MoreHow to save MySQL query output to excel or .txt file?
To save MySQL query output into a text file, we can use the OUTFILE command. Let us first create a table. mysql> create table SaveintoTextFile -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.55 sec) Inserting records into the table. mysql> insert into SaveintoTextFile values(1, 'John'); Query OK, 1 row affected (0.44 sec) mysql> insert into SaveintoTextFile values(101, 'Carol'); Query OK, 1 row affected (0.17 sec) mysql> insert into SaveintoTextFile values(3, 'David'); Query OK, 1 row ...
Read MoreHow to display current connection info in MySQL?
MySQL provides many functions that give the current connection information. For instance, to know about the current user, use the user() function. Syntax mysql> SELECT CURRENT_USER(); Here is the output that displays the name of the current user. +----------------+ | CURRENT_USER() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec) In the above, % tells us about localhost. To check the current connection id, use the following method − mysql> SELECT CONNECTION_ID(); The following is the output that shows the current connection id. ...
Read MoreHow to create boolean column in MySQL with false as default value?
To create a column with ‘false’ as the default value, we can use the concept of “default” at the time of creation of the table. Note − 0 represents false and 1 represents true. Creating a table using “default” false. mysql> create table TrueFalseTable -> ( -> Adult boolean default false -> ); Query OK, 0 rows affected (0.65 sec) Inserting records with no value, since we have set “default” above. mysql> insert into TrueFalseTable values(); Query OK, 1 row affected (0.16 sec) To display records. ...
Read More