module Sequel::MySQL::DatasetMethods

Dataset methods shared by datasets that use MySQL databases.

Constants

MATCH_AGAINST
MATCH_AGAINST_BOOLEAN

Public Instance Methods

calc_found_rows() click to toggle source

Sets up the select methods to use SQL_CALC_FOUND_ROWS option.

dataset.calc_found_rows.limit(10)
# SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 10
    # File lib/sequel/adapters/shared/mysql.rb
708 def calc_found_rows
709   clone(:calc_found_rows => true)
710 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
642 def complex_expression_sql_append(sql, op, args)
643   case op
644   when :IN, :"NOT IN"
645     ds = args[1]
646     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
647       super(sql, op, [args[0], ds.from_self])
648     else
649       super
650     end
651   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
652     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
653       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
654       func = ~func if op == :'!~'
655       return literal_append(sql, func)
656     end
657 
658     sql << '('
659     literal_append(sql, args[0])
660     sql << ' '
661     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
662     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
663     sql << ' '
664     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
665     literal_append(sql, args[1])
666     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
667       sql << " ESCAPE "
668       literal_append(sql, "\\")
669     end
670     sql << ')'
671   when :'||'
672     if args.length > 1
673       sql << "CONCAT"
674       array_sql_append(sql, args)
675     else
676       literal_append(sql, args[0])
677     end
678   when :'B~'
679     sql << "CAST(~"
680     literal_append(sql, args[0])
681     sql << " AS SIGNED INTEGER)"
682   else
683     super
684   end
685 end
constant_sql_append(sql, constant) click to toggle source

MySQL’s CURRENT_TIMESTAMP does not use fractional seconds, even if the database itself supports fractional seconds. If MySQL 5.6.4+ is being used, use a value that will return fractional seconds.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
691 def constant_sql_append(sql, constant)
692   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
693     sql << 'CURRENT_TIMESTAMP(6)'
694   else
695     super
696   end
697 end
delete_from(*tables) click to toggle source

Sets up the select methods to delete from if deleting from a joined dataset:

DB[:a].join(:b, a_id: :id).delete
# DELETE a FROM a INNER JOIN b ON (b.a_id = a.id)

DB[:a].join(:b, a_id: :id).delete_from(:a, :b).delete
# DELETE a, b FROM a INNER JOIN b ON (b.a_id = a.id)
    # File lib/sequel/adapters/shared/mysql.rb
720 def delete_from(*tables)
721   clone(:delete_from=>tables)
722 end
distinct(*args) click to toggle source

Use GROUP BY instead of DISTINCT ON if arguments are provided.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
700 def distinct(*args)
701   args.empty? ? super : group(*args)
702 end
explain(opts=OPTS) click to toggle source

Return the results of an EXPLAIN query as a string. Options:

:extended

Use EXPLAIN EXTENDED instead of EXPLAIN if true.

    # File lib/sequel/adapters/shared/mysql.rb
726 def explain(opts=OPTS)
727   # Load the PrettyTable class, needed for explain output
728   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
729 
730   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
731   rows = ds.all
732   Sequel::PrettyTable.string(rows, ds.columns)
733 end
for_share() click to toggle source

Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.

    # File lib/sequel/adapters/shared/mysql.rb
736 def for_share
737   lock_style(:share)
738 end
full_text_sql(cols, terms, opts = OPTS) click to toggle source

MySQL specific full text search syntax.

    # File lib/sequel/adapters/shared/mysql.rb
746 def full_text_sql(cols, terms, opts = OPTS)
747   terms = terms.join(' ') if terms.is_a?(Array)
748   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
749 end
insert_ignore() click to toggle source

Sets up the insert methods to use INSERT IGNORE. Useful if you have a unique key and want to just skip inserting rows that violate the unique key restriction.

dataset.insert_ignore.multi_insert(
  [{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT IGNORE INTO tablename (name, value) VALUES (a, 1), (b, 2)
    # File lib/sequel/adapters/shared/mysql.rb
759 def insert_ignore
760   clone(:insert_ignore=>true)
761 end
on_duplicate_key_update(*args) click to toggle source

Sets up the insert methods to use ON DUPLICATE KEY UPDATE If you pass no arguments, ALL fields will be updated with the new values. If you pass the fields you want then ONLY those field will be updated. If you pass a hash you can customize the values (for example, to increment a numeric field).

Useful if you have a unique key and want to update inserting rows that violate the unique key restriction.

dataset.on_duplicate_key_update.multi_insert(
  [{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE name=VALUES(name), value=VALUES(value)

dataset.on_duplicate_key_update(:value).multi_insert(
  [{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE value=VALUES(value)

dataset.on_duplicate_key_update(
  value: Sequel.lit('value + VALUES(value)')
).multi_insert(
  [{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE value=value + VALUES(value)
    # File lib/sequel/adapters/shared/mysql.rb
792 def on_duplicate_key_update(*args)
793   clone(:on_duplicate_key_update => args)
794 end
quoted_identifier_append(sql, c) click to toggle source

MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.

    # File lib/sequel/adapters/shared/mysql.rb
797 def quoted_identifier_append(sql, c)
798   sql << '`' << c.to_s.gsub('`', '``') << '`'
799 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
802 def supports_cte?(type=:select)
803   if db.mariadb?
804     type == :select && db.server_version >= 100200
805   else
806     case type
807     when :select, :update, :delete
808       db.server_version >= 80000
809     end
810   end
811 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
814 def supports_derived_column_lists?
815   false
816 end
supports_distinct_on?() click to toggle source

MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.

    # File lib/sequel/adapters/shared/mysql.rb
820 def supports_distinct_on?
821   true
822 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
825 def supports_group_rollup?
826   true
827 end
supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
830 def supports_intersect_except?
831   db.mariadb? && db.server_version >= 100300
832 end
supports_limits_in_correlated_subqueries?() click to toggle source

MySQL does not support limits in correlated subqueries (or any subqueries that use IN).

    # File lib/sequel/adapters/shared/mysql.rb
835 def supports_limits_in_correlated_subqueries?
836   false
837 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
840 def supports_modifying_joins?
841   true
842 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
845 def supports_nowait?
846   db.server_version >= (db.mariadb? ? 100300 : 80000)
847 end
supports_ordered_distinct_on?() click to toggle source

MySQL’s DISTINCT ON emulation using GROUP BY does not respect the query’s ORDER BY clause.

    # File lib/sequel/adapters/shared/mysql.rb
851 def supports_ordered_distinct_on?
852   false
853 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
856 def supports_regexp?
857   true
858 end
supports_skip_locked?() click to toggle source

MySQL 8+ supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
861 def supports_skip_locked?
862   !db.mariadb? && db.server_version >= 80000
863 end
supports_timestamp_usecs?() click to toggle source

Check the database setting for whether fractional timestamps are suppported.

    # File lib/sequel/adapters/shared/mysql.rb
867 def supports_timestamp_usecs?
868   db.supports_timestamp_usecs?
869 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

    # File lib/sequel/adapters/shared/mysql.rb
872 def supports_window_clause?
873   !db.mariadb? && db.server_version >= 80000
874 end
supports_window_functions?() click to toggle source

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
877 def supports_window_functions?
878   db.server_version >= (db.mariadb? ? 100200 : 80000)
879 end
update_ignore() click to toggle source

Sets up the update methods to use UPDATE IGNORE. Useful if you have a unique key and want to just skip updating rows that violate the unique key restriction.

dataset.update_ignore.update(name: 'a', value: 1)
# UPDATE IGNORE tablename SET name = 'a', value = 1
    # File lib/sequel/adapters/shared/mysql.rb
887 def update_ignore
888   clone(:update_ignore=>true)
889 end

Private Instance Methods

check_not_limited!(type) click to toggle source

Allow update and delete for limited datasets, unless there is an offset.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
894 def check_not_limited!(type)
895   super if type == :truncate || @opts[:offset]
896 end
delete_from_sql(sql) click to toggle source

Consider the first table in the joined dataset is the table to delete from, but include the others for the purposes of selecting rows.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
900 def delete_from_sql(sql)
901   if joined_dataset?
902     sql << ' '
903     tables = @opts[:delete_from] || @opts[:from][0..0]
904     source_list_append(sql, tables)
905     sql << ' FROM '
906     source_list_append(sql, @opts[:from])
907     select_join_sql(sql)
908   else
909     super
910   end
911 end
delete_limit_sql(sql)
Alias for: limit_sql
insert_columns_sql(sql) click to toggle source

MySQL doesn’t use the SQL standard DEFAULT VALUES.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
914 def insert_columns_sql(sql)
915   values = opts[:values]
916   if values.is_a?(Array) && values.empty?
917     sql << " ()"
918   else
919     super
920   end
921 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

    # File lib/sequel/adapters/shared/mysql.rb
924 def insert_ignore_sql(sql)
925   sql << " IGNORE" if opts[:insert_ignore]
926 end
insert_on_duplicate_key_update_sql(sql) click to toggle source

MySQL supports INSERT … ON DUPLICATE KEY UPDATE

    # File lib/sequel/adapters/shared/mysql.rb
934 def insert_on_duplicate_key_update_sql(sql)
935   if update_cols = opts[:on_duplicate_key_update]
936     update_vals = nil
937 
938     if update_cols.empty?
939       update_cols = columns
940     elsif update_cols.last.is_a?(Hash)
941       update_vals = update_cols.last
942       update_cols = update_cols[0..-2]
943     end
944 
945     sql << " ON DUPLICATE KEY UPDATE "
946     c = false
947     co = ', '
948     values = '=VALUES('
949     endp = ')'
950     update_cols.each do |col|
951       sql << co if c
952       quote_identifier_append(sql, col)
953       sql << values
954       quote_identifier_append(sql, col)
955       sql << endp
956       c ||= true
957     end
958     if update_vals
959       eq = '='
960       update_vals.map do |col,v| 
961         sql << co if c
962         quote_identifier_append(sql, col)
963         sql << eq
964         literal_append(sql, v)
965         c ||= true
966       end
967     end
968   end
969 end
insert_values_sql(sql) click to toggle source

MySQL doesn’t use the standard DEFAULT VALUES for empty values.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
972 def insert_values_sql(sql)
973   values = opts[:values]
974   if values.is_a?(Array) && values.empty?
975     sql << " VALUES ()"
976   else
977     super
978   end
979 end
join_type_sql(join_type) click to toggle source

Transforms :straight to STRAIGHT_JOIN.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
982 def join_type_sql(join_type)
983   if join_type == :straight
984     'STRAIGHT_JOIN'
985   else
986     super
987   end
988 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

    # File lib/sequel/adapters/shared/mysql.rb
991 def limit_sql(sql)
992   if l = @opts[:limit]
993     sql << " LIMIT "
994     literal_append(sql, l)
995   end
996 end
literal_blob_append(sql, v) click to toggle source

MySQL uses a preceding X for hex escaping strings

     # File lib/sequel/adapters/shared/mysql.rb
1001 def literal_blob_append(sql, v)
1002   if v.empty?
1003     sql << "''"
1004   else
1005     sql << "0x" << v.unpack("H*").first
1006   end
1007 end
literal_false() click to toggle source

Use 0 for false on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1010 def literal_false
1011   '0'
1012 end
literal_float(v) click to toggle source

Raise error for infinitate and NaN values

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1015 def literal_float(v)
1016   if v.infinite? || v.nan?
1017     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
1018   else
1019     super
1020   end
1021 end
literal_string_append(sql, v) click to toggle source

SQL fragment for String. Doubles \ and ‘ by default.

     # File lib/sequel/adapters/shared/mysql.rb
1024 def literal_string_append(sql, v)
1025   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1026 end
literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1029 def literal_true
1030   '1'
1031 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1034 def multi_insert_sql_strategy
1035   :values
1036 end
non_sql_option?(key) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1038 def non_sql_option?(key)
1039   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1040 end
requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1043 def requires_emulating_nulls_first?
1044   true
1045 end
select_calc_found_rows_sql(sql) click to toggle source

MySQL specific SQL_CALC_FOUND_ROWS option

     # File lib/sequel/adapters/shared/mysql.rb
1077 def select_calc_found_rows_sql(sql)
1078   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1079 end
select_lock_sql(sql) click to toggle source

Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1055 def select_lock_sql(sql)
1056   lock = @opts[:lock]
1057   if lock == :share
1058     if !db.mariadb? && db.server_version >= 80000
1059       sql << ' FOR SHARE'
1060     else
1061       sql << ' LOCK IN SHARE MODE'
1062     end
1063   else
1064     super
1065   end
1066 
1067   if lock
1068     if @opts[:skip_locked]
1069       sql << " SKIP LOCKED"
1070     elsif @opts[:nowait]
1071       sql << " NOWAIT"
1072     end
1073   end
1074 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/mysql.rb
1047 def select_only_offset_sql(sql)
1048   sql << " LIMIT "
1049   literal_append(sql, @opts[:offset])
1050   sql << ",18446744073709551615"
1051 end
select_with_sql_base() click to toggle source

Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1082 def select_with_sql_base
1083   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1084 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

    # File lib/sequel/adapters/shared/mysql.rb
929 def update_ignore_sql(sql)
930   sql << " IGNORE" if opts[:update_ignore]
931 end
update_limit_sql(sql)
Alias for: limit_sql
uses_with_rollup?() click to toggle source

MySQL uses WITH ROLLUP syntax.

     # File lib/sequel/adapters/shared/mysql.rb
1087 def uses_with_rollup?
1088   true
1089 end